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
