Hi,
We were looking into optimizing a very long running query and very very surprised by the query plan for a query. To
illustratethe question, I created a sqlfiddle [0].
(1) The query we are running looks like this one:
SELECT *
FROM Foo o
WHERE
o.b = (SELECT b
FROM Foo i
WHERE i.a = o.a
ORDER BY b
LIMIT 1)
AND o.a = 2
(2) We made a minor change to this query by pushing the constant value (2) into the subquery:
SELECT *
FROM Foo o
WHERE
o.b = (SELECT b
FROM Foo i
WHERE i.a = 2
ORDER BY b
LIMIT 1)
AND o.a = 2
The difference in execution time was 5 orders of magnitude. For the former (1) query, Postgres executes the subquery
forevery row, while for the latter query (2) Postgres executes the subquery once and then uses its result.
(3) Finally, we made another modification and removed the subquery. Also, we have an index on Foo(a,b).
SELECT *
FROM Foo o
WHERE
o.b = 2
AND o.a = 2
Is about 2x faster than (2) because it will run as an index scan whereas we couldn't make (2) use the index -- it looks
upthe value of b once, then does a bitmap heap scan followed by a sort.
You might not be able to easily reproduce the difference between (2) and (3) because the decision whether to use an
indexor not depends on the data. However, we would be very interested in learning why (2) is so much faster than (1)
eventhough they seem to be equivalent. Are we missing a corder case in which (1) and (2) have different semantics?
Thank you,
Dominik
[0] http://sqlfiddle.com/#!15/dec73/1