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.
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 |