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)
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;
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
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;
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;
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)
------------------------------------------------------------------------------------------------------------------------------------------------
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.
