Search Postgresql Archives

Equivalence Classes when using IN

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

 



Hi

I have this query where I think it's strange that the join doesn't pull the where condition in since RHS is equal to LHS. It might be easier to expain with an example


Setup
            CREATE TABLE customer (
              customer_id INTEGER PRIMARY KEY
            );

            CREATE TABLE product (
              product_id  INTEGER PRIMARY KEY,
              customer_id INTEGER NOT NULL REFERENCES customer (customer_id)
            );
            
            INSERT INTO customer (SELECT generate_series FROM generate_series(0, 1000000));

            INSERT INTO product (product_id, customer_id) (SELECT generate_series, generate_series / 2 FROM generate_series(0, 2000));


Query

           EXPLAIN ANALYSE
            SELECT *
              FROM customer c
              JOIN (SELECT DISTINCT ON (customer_id) * FROM product ORDER BY customer_id, product_id) p
                ON c.customer_id = p.customer_id
             WHERE c.customer_id IN (500, 501);

                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=172.43..186.25 rows=1 width=12) (actual time=1.350..1.353 rows=2 loops=1)
   Merge Cond: (c.customer_id = product.customer_id)
   ->  Sort  (cost=13.93..13.93 rows=2 width=4) (actual time=0.036..0.036 rows=2 loops=1)
         Sort Key: c.customer_id
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on customer c  (cost=8.58..13.92 rows=2 width=4) (actual time=0.026..0.027 rows=2 loops=1)
               Recheck Cond: (customer_id = ANY ('{500,501}'::integer[]))
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on customer_pkey  (cost=0.00..8.58 rows=2 width=0) (actual time=0.018..0.018 rows=2 loops=1)
                     Index Cond: (customer_id = ANY ('{500,501}'::integer[]))
   ->  Unique  (cost=158.51..169.81 rows=200 width=8) (actual time=0.783..1.221 rows=502 loops=1)
         ->  Sort  (cost=158.51..164.16 rows=2260 width=8) (actual time=0.782..0.929 rows=1003 loops=1)
               Sort Key: product.customer_id, product.product_id
               Sort Method: quicksort  Memory: 142kB
               ->  Seq Scan on product  (cost=0.00..32.60 rows=2260 width=8) (actual time=0.015..0.366 rows=2001 loops=1)
 Planning time: 0.281 ms
 Execution time: 1.432 ms

I would expect that since c.customer_id = p.customer_id then p.customer_id IN (500, 501). If I apply this rule myself, I get a much nicer plan (and it could be even better with an index on product_id).


                                                            QUERY PLAN                                                            
----------------------------------------------------------------------------------------------------------------------------------
 Merge Join  (cost=52.70..53.11 rows=1 width=12) (actual time=0.686..0.693 rows=2 loops=1)
   Merge Cond: (product.customer_id = c.customer_id)
   ->  Unique  (cost=38.77..38.89 rows=22 width=8) (actual time=0.647..0.651 rows=2 loops=1)
         ->  Sort  (cost=38.77..38.83 rows=23 width=8) (actual time=0.646..0.647 rows=4 loops=1)
               Sort Key: product.customer_id, product.product_id
               Sort Method: quicksort  Memory: 25kB
               ->  Seq Scan on product  (cost=0.00..38.25 rows=23 width=8) (actual time=0.331..0.632 rows=4 loops=1)
                     Filter: (customer_id = ANY ('{500,501}'::integer[]))
                     Rows Removed by Filter: 1997
   ->  Sort  (cost=13.93..13.93 rows=2 width=4) (actual time=0.033..0.033 rows=2 loops=1)
         Sort Key: c.customer_id
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on customer c  (cost=8.58..13.92 rows=2 width=4) (actual time=0.025..0.026 rows=2 loops=1)
               Recheck Cond: (customer_id = ANY ('{500,501}'::integer[]))
               Heap Blocks: exact=1
               ->  Bitmap Index Scan on customer_pkey  (cost=0.00..8.58 rows=2 width=0) (actual time=0.018..0.018 rows=2 loops=1)
                     Index Cond: (customer_id = ANY ('{500,501}'::integer[]))
 Planning time: 0.386 ms
 Execution time: 0.774 ms
(19 rows)

Is this because postgres never consider IN clause when building equivalence class's?

Are there any interests in adding such rule?


My idea is to wrap this in a view

          CREATE VIEW view_customer AS
            SELECT c.customer_id,
                   p.product_id
              FROM customer c
         LEFT JOIN (SELECT DISTINCT ON (customer_id) * FROM product ORDER BY customer_id, product_id) p
                ON c.customer_id = p.customer_id

Where the LEFT JOIN can be pruned if there is no explicit need for product_id. Here I loose the power to express that both c.customer_id and p.customer_id is the same.


Best regards

Kim Carlsen


[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