Problem with bitmap-index-scan plan

From: jkapad(at)csd(dot)uoc(dot)gr
To: pgsql-performance(at)postgresql(dot)org
Subject: Problem with bitmap-index-scan plan
Date: 2006-07-05 05:54:44
Message-ID: 1152078884.44ab542441450@ermis.edu.uoc.gr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

If I run the query

explain analyze select * from ind_uni_100 where a=1 and b=1 and c=1

I get the following plan:

Bitmap Heap Scan on ind_uni_100 (cost=942.50..1411.12 rows=125 width=104)
(actual time=72.556..72.934 rows=116 loops=1)
Recheck Cond: ((c = 1) AND (a = 1) AND (b = 1))
-> BitmapAnd (cost=942.50..942.50 rows=125 width=0) (actual
time=72.421..72.421 rows=0 loops=1)
-> Bitmap Index Scan on index_c_ind_uni_100 (cost=0.00..314.00
rows=50000 width=0) (actual time=21.854..21.854 rows=49832 loops=1)
Index Cond: (c = 1)
-> Bitmap Index Scan on index_a_ind_uni_100 (cost=0.00..314.00
rows=50000 width=0) (actual time=22.371..22.371 rows=50319 loops=1)
Index Cond: (a = 1)
-> Bitmap Index Scan on index_b_ind_uni_100 (cost=0.00..314.00
rows=50000 width=0) (actual time=14.226..14.226 rows=49758 loops=1)
Index Cond: (b = 1)
Total runtime: 73.395 ms

Which is quite reasonable.The table has 1.000.000 rows (17.242 pages). From
pg_stat_get_blocks_fetched I can see that there were 102 page requests for
table. So all things seem to work great here!

But if I multiply the size of the table ten-times (10.000.000 rows - 172.414
pages) and run the same query I get:

explain analyze select * from ind_uni_1000 where a=1 and b=1 and c=1

Bitmap Heap Scan on ind_uni_1000 (cost=9369.50..14055.74 rows=1250 width=104)
(actual time=18111.415..176747.937 rows=1251 loops=1)
Recheck Cond: ((c = 1) AND (a = 1) AND (b = 1))
-> BitmapAnd (cost=9369.50..9369.50 rows=1250 width=0) (actual
time=17684.587..17684.587 rows=0 loops=1)
-> Bitmap Index Scan on index_c_ind_uni_1000 (cost=0.00..3123.00
rows=500000 width=0) (actual time=5704.624..5704.624 rows=500910 loops=1)
Index Cond: (c = 1)
-> Bitmap Index Scan on index_a_ind_uni_1000 (cost=0.00..3123.00
rows=500000 width=0) (actual time=6147.962..6147.962 rows=500080 loops=1)
Index Cond: (a = 1)
-> Bitmap Index Scan on index_b_ind_uni_1000 (cost=0.00..3123.00
rows=500000 width=0) (actual time=5767.754..5767.754 rows=500329 loops=1)
Index Cond: (b = 1)
Total runtime: 176753.200 ms

which is slower even than a seq scan. Now I get that there were 131.398 page
requests for table in order to retrieve almost 1250 tuples!Can someone explain
why this is happening? All memory parameters are set to default.

Thanks!

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tomasz Ostrowski 2006-07-05 08:33:59 Re: query very slow when enable_seqscan=on
Previous Message Chris 2006-07-05 00:22:42 Re: optimizing LIKE '%2345' queries