order of nested loop

Поиск
Список
Период
Сортировка
От Joseph Shraibman
Тема order of nested loop
Дата
Msg-id 3EEE6140.3080108@selectacast.net
обсуждение исходный текст
Ответы Re: order of nested loop  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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?


В списке pgsql-general по дате отправления:

Предыдущее
От: Ernest E Vogelsinger
Дата:
Сообщение: Re: Postgres performance comments from a MySQL user
Следующее
От: Jan Wieck
Дата:
Сообщение: Re: full featured alter table?