order of nested loop

From: Joseph Shraibman <jks(at)selectacast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: order of nested loop
Date: 2003-06-17 00:30:56
Message-ID: 3EEE6140.3080108@selectacast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jan Wieck 2003-06-17 00:32:57 Re: full featured alter table?
Previous Message Ernest E Vogelsinger 2003-06-17 00:27:40 Re: Postgres performance comments from a MySQL user