i have query running in postgres 9.3.9 want delete records temp table based on using exists clause matches specific partial index condition created. following related query uses index scan on partial index (abbreviated 'conditions' below):
explain select l.id temp_table l not exists (select 1 customers cx cx.id = l.customer_id , ( conditions )); query plan ---------------------------------------------------------------------------------------------- nested loop anti join (cost=0.42..252440.38 rows=43549 width=4) -> seq scan on temp_table l (cost=0.00..1277.98 rows=87098 width=8) -> index scan using customers__bad on customers cx (cost=0.42..3.35 rows=1 width=4) index cond: (id = l.customer_id) (4 rows) here actual delete query sql. doesn't convinced should use same index scan above, , wonder if it's bug in postgres? notice higher cost:
delete temp_table l exists(select 1 cnu.customers cx cx.id = l.customer_id , ( conditions )); query plan ------------------------------------------------------------------------------------------------ delete on temp_table l (cost=0.42..495426.94 rows=43549 width=12) -> nested loop semi join (cost=0.42..495426.94 rows=43549 width=12) -> seq scan on temp_table l (cost=0.00..1277.98 rows=87098 width=10) -> index scan using customers__bad on customers cx (cost=0.42..6.67 rows=1 width=10) index cond: (id = l.customer_id) (5 rows) to show should possible on delete same plan, had this, , gave me plan wanted, , twice fast query above uses index scan instead of index scan:
with the_right_records (select l.id temp_table l not exists (select 1 cnu.customers cx cx.id = l.customer_id , ( conditions )) delete temp_table t not exists (select 1 the_right_records x x.id = t.id); query plan ------------------------------------------------------------------------------------------------------ delete on temp_table t (cost=253855.72..256902.88 rows=43549 width=34) cte the_right_records -> nested loop anti join (cost=0.42..252440.38 rows=43549 width=4) -> seq scan on temp_table l (cost=0.00..1277.98 rows=87098 width=8) -> index scan using customers__bad on customers cx (cost=0.42..3.35 rows=1 width=4) index cond: (id = l.customer_id) -> hash anti join (cost=1415.34..4462.50 rows=43549 width=34) hash cond: (t.id = x.id) -> seq scan on temp_table t (cost=0.00..1277.98 rows=87098 width=10) -> hash (cost=870.98..870.98 rows=43549 width=32) -> cte scan on the_right_records x (cost=0.00..870.98 rows=43549 width=32) (11 rows) i've noticed same behavior in other examples. have ideas?
Comments
Post a Comment