pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow do I convice postgres to use an index?
5+ messages / 4 participants
[nested] [flat]
* How do I convice postgres to use an index?
@ 2004-07-13 20:29 Vic Ricker <vic@ricker.us>
0 siblings, 1 reply; 5+ messages in thread
From: Vic Ricker @ 2004-07-13 20:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do I convice postgres to use an index?
@ 2004-07-15 14:00 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: SZUCS Gábor <surrano@mailbox.hu>
0 siblings, 1 reply; 5+ messages in thread
From: Achilleus Mantzios @ 2004-07-15 14:00 UTC (permalink / raw)
To: SZUCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do I convice postgres to use an index?
@ 2004-07-15 14:29 SZUCS Gábor <surrano@mailbox.hu>
parent: Vic Ricker <vic@ricker.us>
0 siblings, 1 reply; 5+ messages in thread
From: SZUCS Gábor @ 2004-07-15 14:29 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do I convice postgres to use an index?
@ 2004-07-19 08:47 SZUCS Gabor <surrano@mailbox.hu>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 5+ messages in thread
From: SZUCS Gabor @ 2004-07-19 08:47 UTC (permalink / raw)
To: pgsql-sql
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??
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How do I convice postgres to use an index?
@ 2004-07-20 14:21 Vic Ricker <vic@ricker.us>
parent: SZUCS Gabor <surrano@mailbox.hu>
0 siblings, 0 replies; 5+ messages in thread
From: Vic Ricker @ 2004-07-20 14:21 UTC (permalink / raw)
To: SZUCS Gabor <surrano@mailbox.hu>; +Cc: pgsql-sql
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?
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2004-07-20 14:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-13 20:29 How do I convice postgres to use an index? Vic Ricker <vic@ricker.us>
2004-07-15 14:29 ` SZUCS Gábor <surrano@mailbox.hu>
2004-07-15 14:00 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-07-19 08:47 ` SZUCS Gabor <surrano@mailbox.hu>
2004-07-20 14:21 ` Vic Ricker <vic@ricker.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox