I have two queries that return identical results. One is a SELECT DISTINCT and the other
is the same query without the DISTINCT. The explain for the second one makes it seem as
if it would be faster:
Sort (cost=73560.75..73560.75 rows=3 width=604)
vs.
Sort (cost=67246.81..67246.82 rows=3 width=604)
However in reality the first query runs much faster. The problem is this nested loop:
not distinct:
-> Subquery Scan "*SELECT* 2" (cost=0.00..30602.38 rows=25 width=604)
-> Limit (cost=0.00..30602.38 rows=25 width=604)
-> Nested Loop (cost=0.00..5499145.64 rows=4492 width=604)
================ vs. =================================
distinct:
-> Sort (cost=36903.81..36915.04 rows=4492
width=604)
Sort Key: <snip>
-> Nested Loop (cost=0.00..36631.27
rows=4492 width=604)
In the query with the distinct one table is done first, in the other the order is
reversed. This makes all the difference in the query, because in my test case there is
only one matching entry in one of the tables and that is always the table that determines
the number of rows in the result (and except in pathalogical cases will always be much
lower than the number returned from the first table). So how can I tell postgres which
table to scan in the loop first?
