Search Postgresql Archives

Re: Equivalence Classes when using IN

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



> On 11 Oct 2017, at 21.46, David Rowley <david.rowley@xxxxxxxxxxxxxxx> wrote:
> 
>> On 12 October 2017 at 08:37, Kim Rose Carlsen <krc@xxxxxxxx> 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 on each 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=334 width=12) (actual time=0.075..1.146 rows=100 loops=1)
        ->  Seq Scan on customer  (cost=0.00..21.51 rows=334 width=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.006 rows=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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux