Re: Execution plans for tpc-h

Victor Muntes Mutero <vmuntes@ac.upc.es> writes:
> There is an xjoin (p_partkey = l_partkey)

Not in that form of the query.  You have

    WHERE ( ... ) OR ( ... ) OR ( ... )

If Postgres were to reduce the WHERE expression to CNF, it would discover
that the p_partkey = l_partkey clause is common to all three ORs,
whereupon it would have WHERE (p_partkey = l_partkey) AND (some big OR)
and could use p_partkey = l_partkey as a merge or hash join key.
But I suspect it decides that the WHERE is too complex to try to
simplify to CNF.  We used to apply cnfify() unconditionally, but that
has some unpleasant exponential behavior on large expressions :-(.
What's worse is that DNF is actually the preferred form in some cases
--- especially when a repeated indexscan would be useful.

The heuristics that decide whether to convert to CNF could probably use
further refinement.  If you are interested in playing with them, see
canonicalize_qual() in src/backend/optimizer/prep/prepqual.c.  But it
would not be easy to recognize that this query would be better off in
CNF form: that requires noticing that the OR'd clauses have a lot of
terms in common, and you can't discover that without actually doing
most of the canonicalization work...

            regards, tom lane