New answers tagged

1

Changing NOT IN to NOT EXISTS as suggested by a_horse_with_no_name was the solution: not in () in Postgres is not as efficiently optimized as in Oracle. Try rewriting that as a NOT EXISTS condition. I replaced the subquery AND (bd.INSTANCE_ID, bd.ATTRIBUTE_ID, bd.INSERTED) NOT IN (SELECT ... ) with AND NOT exists ( SELECT 1 FROM my_data dc ...


1

To speed up the inner loop as much as possible, you could use this strange and very specialized index: CREATE INDEX ON my_data ( (CASE WHEN (tenant = 1) THEN 1 WHEN (tenant = 2) THEN 3 ELSE -1 END), inserted ) WHERE modified_record IS NOT NULL AND tenant = ANY ('{1,2}'::bigint[]) AND status = 'DELETED';


0

The question is why the subquery in the second example is executed only once, instead of once for each row, which accounts for the difference in execution time. The reason is that the subquery is not “correlated” with the surrounding query, that is, it does not reference anything from the outer query. The optimizer then executes the query only once (InitPlan)...


0

Forgive the speculation, but I'm going to try answering the question using a different strategy. First, I think the OP was asking about a regular CTE. In PostgreSQL you can use a CTE to feed data to an UPDATE. In my answer, I'm going to find the reference price in a CTE (the "stored value", as OP called it in the question title). Second, the OP's ...


0

(After fixing the invalid reference) The query burns down to: UPDATE changes rt1 SET change = CASE WHEN ( SELECT cp.price FROM clothing_price cp WHERE cp.id = rt1.id -- fixed reference! AND cp.timestamp > now() - interval '24 hours' ORDER BY cp.timestamp LIMIT 1) IS NULL THEN 0 ELSE rt1.price - 1 END WHERE clothing_type = 1;...


Top 50 recent answers are included