Re: table configuration tweak for performance gain. - Mailing list pgsql-general

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