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

The following bug has been logged on the website:

Bug reference:      13640
Logged by:          Jeremy Finzel
Email address:      finzelj@gmail.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.

finzelj@gmail.com writes:
> 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?

No, unfortunately not: the DELETE requires that each table scan return
CTID (for possible EvalPlanQual rechecks), and index-only scans can't
return columns that aren't in the index.

Although come to think of it, we necessarily get the TID from the index
AM, so in principle it should be possible for an index-only scan to
provide that column.  But that's an unimplemented feature, not a bug.

            regards, tom lane