> On 11 Oct 2017, at 21.46, David Rowley <david.rowley@2ndquadrant.com> wrote:
>
>> On 12 October 2017 at 08:37, Kim Rose Carlsen <krc@hiper.dk> wrote:
>>
>>> Yeah. The ORDER BY creates a partial optimization fence, preventing
>>> any such plan from being considered.
>>>>
>>
>> I can see in the general case it semanticly means different things If you allow the WHERE to pass through ORDER BY.
>>
>> A special case can be allowed for WHERE to pass the ORDER BY if the column is part of DISTINCT ON.
>
> Yeah, we do allow predicates to be pushed down in that case.
>
Let's ignore that it's not a very useful query I have written.
Why don't I see that predicate (customer_id) pushed into the outer nested loop so we don't have to sort the whole table
oneach loop.
(See original post and follow up for definitions) QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=139.00..10392.96 rows=668 width=16) (actual time=0.528..35.120 rows=200 loops=1) Join
Filter:(c.customer_id = product.customer_id) Rows Removed by Join Filter: 199900 -> Nested Loop (cost=0.28..199.21
rows=334width=12) (actual time=0.075..1.146 rows=100 loops=1) -> Seq Scan on customer (cost=0.00..21.51
rows=334width=8) (actual time=0.067..0.282 rows=100 loops=1) Filter: (age < 20) Rows Removed by
Filter:901 -> Index Only Scan using customer_pkey on customer c (cost=0.28..0.53 rows=1 width=4) (actual
time=0.006..0.006rows=1 loops=100) Index Cond: (customer_id = customer.customer_id) Heap
Fetches:100 -> Materialize (cost=138.73..173.75 rows=2001 width=8) (actual time=0.005..0.130 rows=2001 loops=100)
-> Sort (cost=138.73..143.73 rows=2001 width=8) (actual time=0.448..0.588 rows=2001 loops=1) Sort Key:
product.customer_id,product.product_id Sort Method: quicksort Memory: 142kB -> Seq Scan on
product (cost=0.00..29.01 rows=2001 width=8) (actual time=0.006..0.215 rows=2001 loops=1)
Planning time: 0.214 ms
Execution time: 35.284 ms
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
