[SQL] How do I convice postgres to use an index?
I apologize for the following stupid question. I have been doing some
searching and haven't found anything really helpful.
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]
Re: [SQL] How do I convice postgres to use an index?
The plan showed that Postgres did the proper cast on the timestamp. I think part of the problem is that I need to increase the memory allocated to effective_cache_size for the optimizer choose the proper method. (Thanks to Richard Huxton for help.) I've read that if it doesn't have enough ram, it will forego the index for a sequential scan.. In my case, that's a very poor decision on the optimizer's part. Disabling enable_seqscan seems to generally fix the problem but I'm afraid that it might degrade performance elsewhere. I have expermiented with the effective_cache_size and some other settings but haven't had as much luck. I think I need more physical ram. Will try that soon. -Vic SZUCS Gabor wrote: re-checked; it's WITHOUT in both version, but it's irrelevant if you give the full spec. Well, then maybe it was a difference between 7.2 and 7.3, but again, it's irrelevant in your case. Have you tried the typecast? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
