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 majordomo@postgresql.org

Re: How do I convice postgres to use an index?

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"
> 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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: How do I convice postgres to use an index?

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"
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

Re: How do I convice postgres to use an index?

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"
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??


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: 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