order of nested loop
От | Joseph Shraibman |
---|---|
Тема | order of nested loop |
Дата | |
Msg-id | 3EEE6140.3080108@selectacast.net обсуждение исходный текст |
Ответы |
Re: order of nested loop
|
Список | 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 по дате отправления: