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/
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 |