Delete using another table with index

I’m trying to delete rows using another table and wondering why postgres doesn’t use an index, here are the steps, on pg10.5:

CREATE TABLE parent (id int);
CREATE TABLE child (pid int);

CREATE INDEX parent_idx ON parent (id);
CREATE INDEX child_idx ON child (pid);

INSERT INTO parent SELECT * FROM generate_series(1, 100000);
INSERT INTO child SELECT * FROM generate_series(1, 100000);
VACUUM ANALYZE parent; VACUUM ANALYZE child;


EXPLAIN DELETE FROM child c USING parent p WHERE p.id = c.pid AND p.id < 10000;
                                             QUERY PLAN                                             
----------------------------------------------------------------------------------------------------
 Delete on child c  (cost=464.31..2383.65 rows=10134 width=12)
   ->  Hash Join  (cost=464.31..2383.65 rows=10134 width=12)
         Hash Cond: (c.pid = p.id)
         ->  Seq Scan on child c  (cost=0.00..1443.00 rows=100000 width=10)
         ->  Hash  (cost=337.64..337.64 rows=10134 width=10)
               ->  Index Scan using parent_idx on parent p  (cost=0.29..337.64 rows=10134 width=10)
                     Index Cond: (id < 10000)

I would have expected Postgres to pick an Index Only Scan using parent_idx followed by an Index Only Scan on child_idx. What am I missing?

It can’t do an index-only scan in this case.

It needs the actual table row, not just the index entry, of the parent row. If you do an EXPLAIN VERBOSE, you will see the p.ctid getting exposed. I believe that is because if the delete encounters a locked row in the child table, it will block until the transaction locking that row finishes. But once that happens, it has to re-check the parent row to check if it was changed by the same transaction that had just changed the child row.

And of course it can’t do an index-only scan on the child, because how would you delete a row in the table without accessing the row in the table?

But anyway, why would doing one index-only scan plus 10,000 individual index scans be faster than one hash join? Hash joins are pretty awesome.