Dear All,
I have a number of complex views for which the typical use is to select
exactly one row by id, e.g. "select * from V where id=nnn". Some of
these selects run orders of magnitude faster than others. Looking at
the output of "explain analyse" it seems that in the fast cases the
"id=nnn" condition is passed down to the lower-level operations, while
in the slower cases the entire view is created and then filtered using
the condition as a final step.
I am trying to narrow down what types of query I can use in the views to
avoid the poor performance. Here are a couple of things that I have
noticed:
- One query had a "distinct on (id)" at the top level. This was only to
cope with an obscure case where what is normally a one-to-one join could
return multiple rows. Removing the "distinct" and discarding the
duplicate rows in the calling code means that the "where id=nnn" is now
applied as a condition for an index scan where it previously wasn't,
reducing execution time by two orders of magnitude. But I can't see a
reason why the "id=nnn" condition couldn't have been used inside the
query, even in the presence of the "distinct" clause.
- In another case I have a LEFT OUTER JOIN which can be made much faster
by instead using a normal JOIN. Unfortunately a normal JOIN doesn't do
what I want, but I can't see why the condition is propogated into the
JOIN but not the LEFT OUTER JOIN. Here is an outline of the query:
D left outer join (M join G on (M.g=G.id)) on (D.id=M.b) where D.id=nnn
That does index scans on M and G and a merge join to create the complete
"M join G" table. On the other hand, if I do
D join (M join G on (M.g=G.id)) on (D.id=M.b) where D.id=nnn
then it does conditional index scans on D.id=nnn and M.b=nnn and a
nested loop join returning one row, followed by a conditional index scan
on G. This is an order of magnitude faster.
I don't think this is a problem with statistics; the row-count estimates
are all reasonable. I imagine that the restriction is something missing
in the query optimiser. Can I rewrite this query somehow? Is there
anything else I can do about it?
This is with 7.4.2.
Cheers, Phil.