> Err, you don't need an index on ctid because the ctid represents that
> physical location of the tuple on disk. ctids are what indexes use to
> refer to tuples...
OK, then how you explain this:
db=# prepare test_001(bigint, bigint, smallint) as
db-# DELETE FROM big_table
db-# WHERE ctid IN
db-# (SELECT ctid FROM big_table
db(# WHERE col1=$2
db(# AND col2 IS NOT NULL
db(# AND col3 =$3
db(# AND col4 <> 'o'
db(# LIMIT 1000);
PREPARE
db=# explain execute test_001(1,1,1);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Nested Loop IN Join (cost=2165.98..24570725.13 rows=29 width=6)
Join Filter: ("outer".ctid = "inner".ctid)
-> Seq Scan on big_table (cost=0.00..1037188.04 rows=36063404
width=6)
-> Materialize (cost=2165.98..2166.27 rows=29 width=6)
-> Subquery Scan "IN_subquery" (cost=0.00..2165.95 rows=29
width=6)
-> Limit (cost=0.00..2165.66 rows=29 width=6)
-> Index Scan using idx_big_table_col2 on
big_table (cost=0.00..2165.66 rows=29 width=6)
Index Cond: (col1 = $2)
Filter: ((col2 IS NOT NULL) AND (col3 = $3)
AND ("col4" <> 'o'::bpchar))
(9 rows)
Cheers,
Csaba.