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

why is bitmap index chosen for this query?

From: Stephen Byers <stephenabyers(at)yahoo(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: why is bitmap index chosen for this query?
Date: 2006-05-18 15:52:04
Message-ID: 20060518155204.66320.qmail@web30701.mail.mud.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-performance
Could someone explain the results of the following?  This is with postgres 8.1.2 on a database that was just vacuum-verbose-analyzed.  I have packets_i4 index which I am expecting to be used with this query but as you can see, I have have to convince its usage by turning off other scans.  The total runtime is pretty drastic when the index is not chosen.  When using a cursor, the query using the index is the only one that provides immediate results.  Also, what is Recheck Cond?
   
  adbs_db=#   \d packets
                        Table "public.packets"
         Column          |          Type          |     Modifiers      
-------------------------+------------------------+--------------------
 system_time_secs        | integer                | not null
 system_time_subsecs     | integer                | not null
 spacecraft_time_secs    | integer                | not null
 spacecraft_time_subsecs | integer                | not null
 mnemonic                | character varying(64)  | 
 mnemonic_id             | integer                | not null
 data_length             | integer                | not null
 data                    | bytea                  | not null
 volume_label            | character varying(128) | not null
 tlm_version_name        | character varying(32)  | not null
 environment_name        | character varying(32)  | not null
 quality                 | integer                | not null default 0
Indexes:
    "packets_i1" btree (volume_label)
    "packets_i4" btree (environment_name, system_time_secs, system_time_subsecs, mnemonic)
    "packets_i5" btree (environment_name, spacecraft_time_secs, spacecraft_time_subsecs, mnemonic)

   
  adbs_db=# explain analyze select spacecraft_time_secs,mnemonic,volume_label
    from packets   where environment_name='PASITCTX01' 
  and system_time_secs>=1132272000 and system_time_secs<=1143244800;
  
               QUERY PLAN                                                                    
-------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on packets  (cost=247201.41..2838497.72 rows=12472989 width=47) (actual time=573856.344..771866.516 rows=13365371 loops=1)
   Recheck Cond: (((environment_name)::text = 'PASITCTX01'::text) AND (system_time_secs >= 1132272000) AND (system_time_secs <= 1143244800))
   ->  Bitmap Index Scan on packets_i4  (cost=0.00..247201.41 rows=12472989 width=0) (actual time=573484.199..573484.199 rows=13365371 loops=1)
         Index Cond: (((environment_name)::text = 'PASITCTX01'::text) AND (system_time_secs >= 1132272000) AND (system_time_secs <= 1143244800))
 Total runtime: 777208.041 ms
(5 rows)

   
  adbs_db=# set enable_bitmapscan to off;
SET

  adbs_db=# explain analyze select spacecraft_time_secs,mnemonic,volume_label
    from packets   where environment_name='PASITCTX01' 
  and system_time_secs>=1132272000 and system_time_secs<=1143244800;
  
                                                              QUERY PLAN                                                               
---------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on packets  (cost=0.00..3045957.30 rows=12472989 width=47) (actual time=58539.693..493056.015 rows=13365371 loops=1)
   Filter: (((environment_name)::text = 'PASITCTX01'::text) AND (system_time_secs >= 1132272000) AND (system_time_secs <= 1143244800))
 Total runtime: 498620.963 ms
(3 rows)

   
  adbs_db=# set enable_seqscan to off;
  SET
   
  adbs_db=# explain analyze select spacecraft_time_secs,mnemonic,volume_label
    from packets   where environment_name='PASITCTX01' 
  and system_time_secs>=1132272000 and system_time_secs<=1143244800;
  

              QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using packets_i4 on packets  (cost=0.00..19908567.85 rows=12472989 width=47) (actual time=47.691..206028.754 rows=13365371 loops=1)
   Index Cond: (((environment_name)::text = 'PASITCTX01'::text) AND (system_time_secs >= 1132272000) AND (system_time_secs <= 1143244800))
 Total runtime: 211644.843 ms
(3 rows)


		
---------------------------------
Blab-away for as little as 1ยข/min. Make  PC-to-Phone Calls using Yahoo! Messenger with Voice.

Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2006-05-18 15:59:06
Subject: Re: why is bitmap index chosen for this query?
Previous:From: Chris MckenzieDate: 2006-05-18 15:20:17
Subject: Re: Performance/Maintenance test result collection

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