Problem with bitmap-index-scan plan - Mailing list pgsql-performance

From jkapad@csd.uoc.gr
Subject Problem with bitmap-index-scan plan
Date
Msg-id 1152078884.44ab542441450@ermis.edu.uoc.gr
Whole thread Raw
Responses Re: Problem with bitmap-index-scan plan  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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!



pgsql-performance by date:

Previous
From: Chris
Date:
Subject: Re: optimizing LIKE '%2345' queries
Next
From: Markus Schaber
Date:
Subject: Re: Is postgresql ca do the job for software deployed in