Hello,
probably you remember my crazy idea involving using indexes
directly in scans (and resulting speedup).
The idea was given to me by experiences with M$SQL (it is
yes another M$ soft but its planner is probably better
than pg's - no flames please).
Because I studied M$ again I've got another ideas.
Every SQL query can be probably translated into joins without
need for "nested subquery" executor node.
In M$SQL7 each join has at least two properties: logical
and physical type. Physical types can be: {nested loop,
hash,merge} join and logical: {left,full,inner,semi-inner,anti-semi}.
It is the same in pgsql except for semi joins.
Inner semijoin scans its left input outputting all rows which
has its pair in right input but doesn't duplicate result when
there are duplicates at right.
The WHERE IN(select...), corelated EXISTS and ANY are
converted to it. This semijoin is simple to efectively implement
for all physical join types.
NOT IN, NOT EXISTS and ALL uses anti-semi-inner-join. The
join outputs lefts which can't be paired and don't duplicate
others. Again, simple implementation.
As I studied outputs from M$ planner, it uses those joins and
later tries to find optimal plan by combining ALL joins.
In pg we can't cross subplan node in optimizing (AFAIK). So
we can't swap relations in outer and inner plan even if it
would lead into mode effective plan.
The result is that in M$SQL7 almost all plans with [NOT]{IN,EXISTS}
I tried was much faster both in clean time of run and in number
of logical reads/scans.
Have anyone thought about it ?
regards, devik