Skip site navigation (1) Skip section navigation (2)

Bitmap indexes etc.

From: Ivan Voras <ivoras(at)fer(dot)hr>
To: pgsql-performance(at)postgresql(dot)org
Subject: Bitmap indexes etc.
Date: 2005-12-26 21:32:13
Message-ID: 20051226222915.G87624@geri.cc.fer.hr (view raw or flat)
Thread:
Lists: pgsql-performance
Hi!

This is not actually a question about performance, but an inquiry to help 
me understand what is going on. Below this text are two EXPLAIN ANALYZE 
outputs, before and after VACUUM ANALYZE was run. I have several questions 
about the proposed plans (mostly the first one). There is only one table 
in the query, "layout", containing ~10k rows. In it, for each "page_id" 
there are several (1-10) rows (i.e. there are around 10000/5 unique 
page_id values). There's an index on "page_id" and I've upped statistics 
collection on it to 150 at table creation time because sometimes the 
planner didn't use the index at all.
This is PostgreSQL 8.1.0.

- what does "Bitmap Heap Scan" phase do?
- what is "Recheck condition" and why is it needed?
- why are proposed "width" fields in the plan different between the two
   plans?
   (actually, a nice explanation what exactly are those widths would also
   be nice :) )
- I thought "Bitmap Index Scan" was only used when there are two or more
   applicable indexes in the plan, so I don't understand why is it used
   now?


cw2=> explain analyze select * from layout where page_id=10060;
                                                        QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on layout  (cost=2.15..64.21 rows=43 width=60) (actual 
time=0.043..0.053 rows=4 loops=1)
    Recheck Cond: (page_id = 10060)
    ->  Bitmap Index Scan on layout_page_id  (cost=0.00..2.15 rows=43 
width=0) (actual time=0.034..0.034 rows=4 loops=1)
          Index Cond: (page_id = 10060)
  Total runtime: 0.112 ms
(5 rows)

cw2> VACUUM ANALYZE;

cw2=> explain analyze select * from layout where page_id=10060;
                                                        QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------
  Index Scan using layout_page_id on layout  (cost=0.00..12.14 rows=4 
width=42) (actual time=0.014..0.025 rows=4 loops=1)
    Index Cond: (page_id = 10060)
  Total runtime: 0.076 ms
(3 rows)



-- 
Preserve wildlife -- pickle a squirrel today!


Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2005-12-26 21:36:30
Subject: Re: Performance hit on large row counts
Previous:From: David ScottDate: 2005-12-26 21:03:34
Subject: Performance hit on large row counts

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group