[GENERAL] Equivalence Classes when using IN - Mailing list pgsql-general

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);

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).

Are there any interests in adding such rule?

My idea is to wrap this in a view

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