The problem is that postgres (7.4.2) keeps choosing to do a sequential scan on a table when an index scan would be significantly faster.
The queries that I'm using look at daily statistics from events logged by our Checkpoint firewall and generate graphs. Since they are bit complicated, I simplified it to "select count(*) from log where timestamp>='7/12/2004'" for testing.
The table looks like this:
Column | Type | Modifiers
--------------+-----------------------------+-----------
loc | integer |
src | inet |
dst | inet |
interface | character varying(10) |
direction | character varying(8) |
proto | character varying(4) |
service | integer |
icmp_code | integer |
sport | integer |
timestamp | timestamp without time zone |
rule | character varying(8) |
message_info | text |
action | character varying(16) |
icmp_type | integer |
orig | inet |
Indexes:
"log_dst_key" btree (dst)
"log_src_key" btree (src)
"log_timestamp_key" btree ("timestamp")To test, I started with vacuum analyze. (My table has approximately 5.8M rows.)
fw1=# select count(*) from log where timestamp>='7/12/2004'; count -------- 246763 (1 row)
Time: 161199.955 ms fw1=# set enable_seqscan='off'; SET Time: 47.662 ms fw1=# select count(*) from log where timestamp>='7/12/2004'; count -------- 247149 (1 row)
Time: 12428.210 ms
Notice the execution time differences.
The query plan before turning enable_seqscan off looks like this:
Aggregate (cost=208963.26..208963.26 rows=1 width=0)
-> Seq Scan on log (cost=0.00..208380.89 rows=232948 width=0)
Filter: ("timestamp" >= '2004-07-12 00:00:00'::timestamp without time zone)
Any suggestions?
Thanks, -Vic
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
