Re: Execution plans for tpc-h - Mailing list pgsql-general

From Tom Lane
Subject Re: Execution plans for tpc-h
Date
Msg-id 19826.984501378@sss.pgh.pa.us
Whole thread Raw
In response to Execution plans for tpc-h  (Victor Muntes Mutero <vmuntes@ac.upc.es>)
List pgsql-general
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

pgsql-general by date:

Previous
From: Frank Miles
Date:
Subject: Re: pqReadData() - backend unexpectedly closed the channel
Next
From: Tom Lane
Date:
Subject: Re: "critical mass" reached?