Re: table configuration tweak for performance gain.

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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Tuesday, 16 November 2004 2:26
> To: Michael Fuhr
> Cc: Harvey, Allan AC; pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] table configuration tweak for
> performance gain.
>
>
> Michael Fuhr <mike(at)fuhr(dot)org> 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
>