Extremely slow Postgres query that runs fast in Oracle

Question

So i just started working in PostgreSQL after some experience with Oracle and I have this query, that in Oracle returns in 200ms and in Postgres returns in 1.40 mins. The culprit seems to be

AND product_cost_view.product_type_id = product.product_type_id

When i remove this portion or hardcode product_cost_view.product_type_id with some ID, it runs fast. Explain plan didn't seem give and insight, it just says INDEX SCAN ON TABLE product TOTAL COST 776403 1913 ROWS.

Yes, product_cost_view is a view, I've also remarked that if i replace that view with a table that also has product_type_id then it also works fast. I tried using CTE and subselects in 100 different forms but when i use that product.product_type_id in the where clause with that view it just works hellish slow and i can't see what I miss. Thanks in advance :) P.S. Yes, i have the exact same data and indexes in both databases

SELECT COUNT(*)
FROM product
WHERE user_id = 1000000
  AND (product_id IN (SELECT DISTINCT product_id
                        FROM product_cost_view
                        WHERE user_id = 1000000
                          AND cost_type = 'X'
                          AND product_cost_view.product_type_id = product.product_type_id)
    );

Answer 1

Could you try this variant:

SELECT COUNT(DISTINCT P.product_id)
FROM product P
INNER JOIN product_cost_view PC
    ON P.product_id = PC.product_id
    AND P.user_id = PC.user_id
    AND P.product_type_id = PC.product_type_id
WHERE P.user_id = 1000000
    AND PC.cost_type = 'X'

Answer 2

Because of the DISTINCT, PostgreSQL cannot flatten the subquery into a join, so you are running the subquery for every row found in product.

Hard to say for certain without seeing the execution plan, but this should be faster:

SELECT COUNT(*)
FROM product AS p
WHERE p.user_id = 1000000
  AND EXISTS (SELECT 1 FROM product_cost_view AS pc
              WHERE pc.product_type_id = p.product_type_id
                AND pc.product_id = p.product_id
                AND pc.user_id = 1000000
                AND pc.cost_type = 'X');