hi
did:
create table test as select i as id, i || ' ' || repeat('depesz', 100) as z from generate_series(1,30000000) i;
create index q on test (id);
vacuum verbose analyze test;
vacuum verbose analyze test;
then I checked that index only scans work:
$ explain analyze select id from test order by id desc limit 5;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..0.50 rows=5 width=4) (actual time=0.036..0.040 rows=5 loops=1)
-> Index Only Scan Backward using q on test (cost=0.00..3029050.54 rows=30000000 width=4) (actual
time=0.033..0.034rows=5 loops=1)
Total runtime: 0.061 ms
(3 rows)
but when I tried it with a bit more complex query, it failed?
$ explain analyze select id from test where id =
any('{29803895,11161654,17844200,2064469,10910969,6182261,12733552,3820132,3297207,6508781,5831136,4716412,3044368,14719611,8523750,21748704,19193684,13331907,4850220,10061877}'::int4[]);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test (cost=88.47..169.00 rows=20 width=4) (actual time=0.126..0.149 rows=20 loops=1)
Recheck Cond: (id = ANY
('{29803895,11161654,17844200,2064469,10910969,6182261,12733552,3820132,3297207,6508781,5831136,4716412,3044368,14719611,8523750,21748704,19193684,13331907,4850220,10061877}'::integer[]))
-> Bitmap Index Scan on q (cost=0.00..88.46 rows=20 width=0) (actual time=0.117..0.117 rows=20 loops=1)
Index Cond: (id = ANY
('{29803895,11161654,17844200,2064469,10910969,6182261,12733552,3820132,3297207,6508781,5831136,4716412,3044368,14719611,8523750,21748704,19193684,13331907,4850220,10061877}'::integer[]))
Total runtime: 0.177 ms
(5 rows)
it is selecting 20 rows out of 30 million. why is it:
1. not using index only scan
2. not using even normal index scan?
Best regards,
depesz
--
The best thing about modern society is how easy it is to avoid contact with it.
http://depesz.com/
