2011/10/8 Thom Brown <thom@xxxxxxxxx>: > On 8 October 2011 18:53, Pavel Stehule <pavel.stehule@xxxxxxxxx> wrote: >> Hello >> >> 2011/10/8 Tom Lane <tgl@xxxxxxxxxxxxx>: >>> hubert depesz lubaczewski <depesz@xxxxxxxxxx> writes: >>>> 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? >>> >>> It thinks the bitmap scan is cheaper. Whether that's true or not is not >>> very clear, but nobody is claiming that the costing of index-only scans >>> is accurate yet. >>> >> >> I did a few tests and bitmap scan is faster. Maybe there is a some >> issue. In very simple test (and very syntetic test) >> >> create table omega(a int); >> insert into omega select (random()*10000)::int from generate_series(1,400000); >> >> select count(*) from omega where a = 100; >> >> and index scan is faster than index only scan. There is lot of >> duplicates. When I used a bigger range, a speed of bitmap index, index >> only scan and index scan is similar - but index scan was faster >> everywhere. > > Here, index-only scan is massively faster than any other scan: > > test=# explain analyse select count(thing) from stuff where thing = 14; > > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=99814.38..99814.39 rows=1 width=4) (actual > time=337.506..337.506 rows=1 loops=1) > -> Index Only Scan using idx_stuff_thing on stuff > (cost=0.00..99336.88 rows=191000 width=4) (actual > time=155.955..315.106 rows=196828 loops=1) > Index Cond: (thing = 14) > Total runtime: 337.639 ms > (4 rows) > > test=# set enable_indexonlyscan to false; > SET > test=# explain analyse select count(thing) from stuff where thing = 14; > > QUERY PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------ > Aggregate (cost=99814.38..99814.39 rows=1 width=4) (actual > time=164882.528..164882.528 rows=1 loops=1) > -> Index Scan using idx_stuff_thing on stuff (cost=0.00..99336.88 > rows=191000 width=4) (actual time=0.184..164494.806 rows=196828 > loops=1) > Index Cond: (thing = 14) > Total runtime: 164882.666 ms > (4 rows) > > test=# set enable_indexonlyscan to false; > SET > test=# set enable_indexscan to false; > SET > test=# explain analyse select count(thing) from stuff where thing = 14; > > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=170553.91..170553.92 rows=1 width=4) (actual > time=154102.221..154102.222 rows=1 loops=1) > -> Bitmap Heap Scan on stuff (cost=2004.91..170076.41 rows=191000 > width=4) (actual time=482.974..153730.892 rows=196828 loops=1) > Recheck Cond: (thing = 14) > -> Bitmap Index Scan on idx_stuff_thing (cost=0.00..1957.16 > rows=191000 width=0) (actual time=421.854..421.854 rows=196828 > loops=1) > Index Cond: (thing = 14) > Total runtime: 154107.415 ms > (6 rows) > > test=# set enable_indexonlyscan to false; > SET > test=# set enable_indexscan to false; > SET > test=# set enable_bitmapscan to false; > SET > test=# explain analyse select count(thing) from stuff where thing = 14; > QUERY PLAN > -------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=812977.50..812977.51 rows=1 width=4) (actual > time=121296.897..121296.897 rows=1 loops=1) > -> Seq Scan on stuff (cost=0.00..812500.00 rows=191000 width=4) > (actual time=67.105..121215.296 rows=196828 loops=1) > Filter: (thing = 14) > Rows Removed by Filter: 14803172 > Total runtime: 121296.999 ms > (5 rows) > > Note: buffer cache cleared between queries. I did it. It is strange, so your times are significantly slower than I have. Have you enabled asserts? Pavel > > -- > Thom Brown > Twitter: @darkixion > IRC (freenode): dark_ixion > Registered Linux user: #516935 > > EnterpriseDB UK: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
