It would appear that in 7.4 the order of clauses in WHERE affects the execution time (not output if AND).
I would think that a simple optimization would be to push off evaluation of a subplan whenever possible by re-arranging AND statements. In the below example, it gives an order of magnitude speed increase on a small dataset.
EXPLAIN ANALYZE
SELECT ss.service_id
FROM service.service AS ss
JOIN account.account AS a ON (ss.account_id = a.parent_account_id)
WHERE NOT EXISTS (SELECT subservice_id
FROM service.combination
JOIN service.service using (service_id)
WHERE account_id = a.account_id
AND subservice_id = ss.service_id)
AND (account_id_exposed_to IS NULL OR account_id_exposed_to = a.account_id)
AND a.account_id = 8219;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=3.17..5755.34 rows=22 width=4) (actual time=46.88..4552.81 rows=15
loops=1)
Hash Cond: ("outer".account_id = "inner".parent_account_id)
Join Filter: ((NOT (subplan)) AND (("outer".account_id_exposed_to IS NULL) OR
("outer".account_id_exposed_to = "inner".account_id)))
-> Seq Scan on service ss (cost=0.00..46.16 rows=916 width=12) (actual
time=0.20..11.53 rows=916 loops=1)
-> Hash (cost=3.16..3.16 rows=1 width=8) (actual time=0.12..0.12 rows=0 loops=1)
-> Index Scan using account_pkey on account a (cost=0.00..3.16 rows=1
width=8) (actual time=0.06..0.08 rows=1 loops=1)
Index Cond: (account_id = 8219)
SubPlan
-> Nested Loop (cost=0.00..259.12 rows=3 width=4) (actual time=12.93..12.93
rows=0 loops=350)
-> Seq Scan on combination (cost=0.00..155.21 rows=26 width=8) (actual
time=6.13..12.66 rows=4 loops=350)
Filter: (subservice_id = $1)
-> Index Scan using service_pkey on service (cost=0.00..3.98 rows=1
width=4) (actual time=0.04..0.04 rows=0 loops=1553)
Index Cond: ("outer".service_id = service.service_id)
Filter: (account_id = $0)
Total runtime: 4553.59 msec
(15 rows)
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=3.17..5755.34 rows=22 width=4) (actual time=17.27..449.76 rows=15
loops=1)
Hash Cond: ("outer".account_id = "inner".parent_account_id)
Join Filter: ((("outer".account_id_exposed_to IS NULL) OR
("outer".account_id_exposed_to = "inner".account_id)) AND (NOT (subplan)))
-> Seq Scan on service ss (cost=0.00..46.16 rows=916 width=12) (actual
time=0.20..9.65 rows=916 loops=1)
-> Hash (cost=3.16..3.16 rows=1 width=8) (actual time=0.12..0.12 rows=0 loops=1)
-> Index Scan using account_pkey on account a (cost=0.00..3.16 rows=1
width=8) (actual time=0.06..0.08 rows=1 loops=1)
Index Cond: (account_id = 8219)
SubPlan
-> Nested Loop (cost=0.00..259.12 rows=3 width=4) (actual time=5.83..5.83
rows=1 loops=74)
-> Seq Scan on combination (cost=0.00..155.21 rows=26 width=8) (actual
time=4.81..5.42 rows=7 loops=74)
Filter: (subservice_id = $1)
-> Index Scan using service_pkey on service (cost=0.00..3.98 rows=1
width=4) (actual time=0.04..0.04 rows=0 loops=554)
Index Cond: ("outer".service_id = service.service_id)
Filter: (account_id = $0)
Total runtime: 450.54 msec
(15 rows)
EXPLAIN ANALYZE
SELECT ss.service_id
FROM service.service AS ss
JOIN account.account AS a ON (ss.account_id = a.parent_account_id)
WHERE (account_id_exposed_to IS NULL OR account_id_exposed_to = a.account_id)
AND NOT EXISTS (SELECT subservice_id
FROM service.combination
JOIN service.service using (service_id)
WHERE account_id = a.account_id
AND subservice_id = ss.service_id)
AND a.account_id = 8219;
signature.asc
Description: This is a digitally signed message part
