[9.2devel] why it doesn't do index scan only?

From: hubert depesz lubaczewski <depesz(at)depesz(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: [9.2devel] why it doesn't do index scan only?
Date: 2011-10-08 16:39:12
Message-ID: 20111008163911.GA26415@depesz.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.034 rows=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/

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2011-10-08 16:47:46 Re: [9.2devel] why it doesn't do index scan only?
Previous Message René Fournier 2011-10-08 16:34:53 Re: Best PostGIS function for finding the nearest line segment to a given point