This beaviour is normal. Bitmap index scan is faster than index scan.
2011/10/8, hubert depesz lubaczewski <depesz@xxxxxxxxxx>:
> 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/
>
> --
> Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--
------------
pasman
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
