Some performance oddities in numerical comparisons on merge tables

Marc Ballarin marc.ballarin at 1und1.de
Mon Apr 18 12:26:55 CEST 2016


Hi,

Am 17.04.2016 um 12:31 schrieb Niels Nes:
> Well not that surprising. The choice on which operator in MonetDB
> isn't based on the content of the table but using relatively simpel
> rules. A equality is done before a range, and a range before an in. So
> in case of the in, the very selective range query is done first. In
> case of the equality, the equality is done first. Niels 
Ok, this matches the trace Stefan Manegold suggested:

Doing "select sum(reads), sum(minflt) from sys.tracelog()"...
...for the query using "in": 268896, 1234
...for the query using "equals": 16707488, 16736

PLAN SELECT also shows the different ordering of conditions.

Is there any way to give hints to the query optimizer? In my case, I
have a pretty good idea how most columns look, at least those usually
used in conditions, and I know which conditions will reduce the result
set the most.

Also, is there any list of precedences?
So far, I have seen: equality > range > like > in > is null > user
defined functions.

Regards,
Marc


More information about the users-list mailing list