X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id AE39A9FA293 for ; Thu, 18 May 2006 12:52:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 31050-03 for ; Thu, 18 May 2006 12:52:06 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web30701.mail.mud.yahoo.com (web30701.mail.mud.yahoo.com [68.142.200.134]) by postgresql.org (Postfix) with SMTP id 2E7A99F9C18 for ; Thu, 18 May 2006 12:52:06 -0300 (ADT) Received: (qmail 66325 invoked by uid 60001); 18 May 2006 15:52:05 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Subject:To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=tClIw9n3byzbbC/0YSrAqD5IkzpcAljk9Fcl4z23OHUxJQkak62TOKio3h7iuV9gRzZYEr0MzOTep/ad2eXF1CLn/S3E0crX8R/nYx9DFxnkHqsMM7tVO/H4klp645hqXUK5dxHrSZHcXqQfwwCw+QOS9FuzOe5cOXAGYJB27SM= ; Message-ID: <20060518155204.66320.qmail@web30701.mail.mud.yahoo.com> Received: from [206.83.255.230] by web30701.mail.mud.yahoo.com via HTTP; Thu, 18 May 2006 08:52:04 PDT Date: Thu, 18 May 2006 08:52:04 -0700 (PDT) From: Stephen Byers Subject: why is bitmap index chosen for this query? To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1292529740-1147967524=:66085" Content-Transfer-Encoding: 8bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/390 X-Sequence-Number: 19177 --0-1292529740-1147967524=:66085 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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. --0-1292529740-1147967524=:66085 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit
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. --0-1292529740-1147967524=:66085--