BUG #13640: Delete isn't using available Index Only Scan

From: finzelj(at)gmail(dot)com
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #13640: Delete isn't using available Index Only Scan
Date: 2015-09-25 18:38:59
Message-ID: 20150925183859.26929.93860@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 13640
Logged by: Jeremy Finzel
Email address: finzelj(at)gmail(dot)com
PostgreSQL version: 9.3.9
Operating system: Linux
Description:

I have a query running in Postgres 9.3.9 where I want to delete some records
from a temp table based on using an EXISTS clause that matches a specific
partial index condition I created. The following related query uses an Index
Only Scan on this partial index (abbreviated as 'conditions' below):

EXPLAIN
SELECT l.id
FROM temp_table l
WHERE NOT EXISTS
(SELECT 1
FROM customers cx
WHERE cx.id = l.customer_id
AND ( 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 Only 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 is the actual delete query SQL. This doesn't but I am convinced should
use the same Index Only Scan as above, and I wonder if it's a bug? Notice
the higher cost:

DELETE
FROM temp_table l
WHERE EXISTS(SELECT 1
FROM cnu.customers cx
WHERE cx.id = l.customer_id
AND ( 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 that it should be possible on delete to get the same plan, I had to
do this, and it gave me the plan I wanted, and was twice as fast as the
query above that uses an Index Scan instead of Index Only Scan:

WITH the_right_records AS
(SELECT l.id
FROM temp_table l
WHERE NOT EXISTS
(SELECT 1
FROM cnu.customers cx
WHERE cx.id = l.customer_id
AND ( conditions ))

DELETE FROM temp_table t
WHERE NOT EXISTS (SELECT 1
FROM the_right_records x
WHERE 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 Only 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 this same behavior in other examples.

Thank you.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2015-09-25 19:18:28 Re: BUG #13638: Exception texts from plperl has bad encoding
Previous Message Tom Lane 2015-09-25 15:16:44 Re: BUG #13636: psql numericlocale adds comma where it ought not