why is bitmap index chosen for this query?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux