Date: Mon, 26 Dec 2005 22:32:13 +0100 (CET)
From: Ivan Voras <ivoras@fer.hr>
To: pgsql-performance@postgresql.org
Subject: Bitmap indexes etc.
Message-ID: <20051226222915.G87624@geri.cc.fer.hr>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

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!