why is bitmap index chosen for this query? - Mailing list 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=# 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)