Tom, Michael,
Thanks for your interests. My original post was scant
on detail as I was unsure if I had found the right place.
It appears I have, so...
Version is 7.4.5
Table size these tests were carried out on:-
mill2=> select count(*) from history\g
count
--------
258606
(1 row)
Before index:-
mill2=> \d history
Table "public.history"
Column | Type | Modifiers
-----------+-----------------------------+-----------
pointname | character varying(32) | not null
parameter | character varying(8) | not null
value | double precision | not null
dt | timestamp without time zone | not null
snip.....
WARNING: skipping "pg_conversion" --- only table or database owner can analyze it
WARNING: skipping "pg_depend" --- only table or database owner can analyze it
ANALYZE
mill2=> explain select value from history where pointname = 'MILL2-SPEED' and dt < now() - interval '5 minutes'\g
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Seq Scan on history (cost=0.00..8276.82 rows=8982 width=8)
Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND ((dt)::timestamp with time zone < (now() - '00:05:00'::interval)))
(2 rows)
After index:-
mill2=> create index dtindex on history( dt )\g
CREATE INDEX
mill2=> \d history
Table "public.history"
Column | Type | Modifiers
-----------+-----------------------------+-----------
pointname | character varying(32) | not null
parameter | character varying(8) | not null
value | double precision | not null
dt | timestamp without time zone | not null
Indexes:
"dtindex" btree (dt)
snip....
WARNING: skipping "pg_conversion" --- only table or database owner can analyze it
WARNING: skipping "pg_depend" --- only table or database owner can analyze it
ANALYZE
mill2=> explain select value from history where pointname = 'MILL2-SPEED' and dt < now() - interval '5 minutes'\g
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Seq Scan on history (cost=0.00..8263.19 rows=9342 width=8)
Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND ((dt)::timestamp with time zone < (now() - '00:05:00'::interval)))
(2 rows)
>don't recommend turning off enable_seqscan as a production solution
On your advise I did not go there.
On using BETWEEN:-
mill2=> select value from history where pointname = 'MILL2-SPEED' and dt between now() and now() - interval '5 minutes'\g
value
-------
(0 rows)
mill2=> select value from history where pointname = 'MILL2-SPEED' and dt < now() - interval '5 minutes'\g
value
---------
85606.9
85606.9
85606.9
85606.9
85606.9
85606.9
etc.....
I have obviously used it wrong but cannot see how/why.
Thanks again.
Allan
> -----Original Message-----
> From: Tom Lane [mailto:tgl@xxxxxxxxxxxxx]
> Sent: Tuesday, 16 November 2004 2:26
> To: Michael Fuhr
> Cc: Harvey, Allan AC; pgsql-general@xxxxxxxxxxxxxx
> Subject: Re: table configuration tweak for
> performance gain.
>
>
> Michael Fuhr <mike@xxxxxxxx> writes:
> > On Mon, Nov 15, 2004 at 04:25:59PM +1100, Harvey, Allan AC wrote:
> >> I created an index on the dt column, ran ANALYSE then,
> >> EXPLAIN for some queries.
> >> The returned plan was always sequential search.
>
> > Could you post a query and the EXPLAIN ANALYZE output? We could
> > probably give better advice if we could see what's happening.
>
> Also, let's see EXPLAIN ANALYZE results after setting
> enable_seqscan to
> OFF. If that doesn't force it into an indexscan, then you have got
> more fundamental issues (perhaps a datatype mismatch). Note that I
> don't recommend turning off enable_seqscan as a production solution;
> but it's a useful tool for debugging.
>
> regards, tom lane
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
