Thread: 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 |
timestampwithout 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
Try casting the constant to the type of the field, i.e. WHERE timestamp >= '7/12/2004'::"timestamp without time zone" (iirc the quotes are necessary) Also, I'd try to avoid naming attributes like (built-in) types. (iirc "timestamp" is a type without time zone in 7.3, and with time zone in 7.4 :) "timestamptz" is the opposite in both cases) G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "Vic Ricker" <vic@ricker.us> Sent: Tuesday, July 13, 2004 10:29 PM > 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.
O kyrios SZUCS Gαbor egrapse stis Jul 15, 2004 :
> Try casting the constant to the type of the field, i.e.
>
> WHERE timestamp >= '7/12/2004'::"timestamp without time zone"
>
> (iirc the quotes are necessary)
>
> Also, I'd try to avoid naming attributes like (built-in) types. (iirc
> "timestamp" is a type without time zone in 7.3, and with time zone in 7.4 :)
^^^^^^^^^^^^^^^^^^^^^^
Are you sure about it??
> "timestamptz" is the opposite in both cases)
>
> G.
> %----------------------- cut here -----------------------%
> \end
>
> ----- Original Message -----
> From: "Vic Ricker" <vic@ricker.us>
> Sent: Tuesday, July 13, 2004 10:29 PM
>
>
> > 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--
-Achilleus
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? G. %----------------------- cut here -----------------------% \end ----- Original Message ----- From: "Achilleus Mantzios" <achill@matrix.gatewaynet.com> Sent: Thursday, July 15, 2004 4:00 PM > > Also, I'd try to avoid naming attributes like (built-in) types. (iirc > > "timestamp" is a type without time zone in 7.3, and with time zone in 7.4 :) > ^^^^^^^^^^^^^^^^^^^^^^ > > Are you sure about it??
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? > > >