Re: table configuration tweak for performance gain.

Michael,

I don't mind at all.

3.244 ms compared to 15706.179 ms.
A sizeable difference.

Screen scraps follow.

Allan

mill2=> set enable_seqscan=off\g
SET
mill2=> explain analyse select count(*) from history where pointname = 'MILL2-SPEED' and dt > (now() - interval '5 minutes')::timestamp\g
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=34815.05..34815.05 rows=1 width=0) (actual time=2.267..2.275 rows=1 loops=1)
-> Index Scan using dtindex on history (cost=0.00..34783.32 rows=12690 width=0) (actual time=1.931..1.931 rows=0 loops=1)
Index Cond: (((pointname)::text = 'MILL2-SPEED'::text) AND (dt > ((now() - '00:05:00'::interval))::timestamp without time zone))
Total runtime: 3.244 ms
(4 rows)

mill2=> set enable_seqscan=on\g
SET
mill2=> select count(*) from history where pointname = 'MILL2-SPEED' and dt > (now() - interval '5 minutes')::timestamp\g
count
-------
0
(1 row)

mill2=> explain analyse select count(*) from history where pointname = 'MILL2-SPEED' and dt > (now() - interval '5 minutes')::timestamp\g
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10163.50..10163.50 rows=1 width=0) (actual time=15705.388..15705.395 rows=1 loops=1)
-> Seq Scan on history (cost=0.00..10131.77 rows=12690 width=0) (actual time=15705.286..15705.286 rows=0 loops=1)
Filter: (((pointname)::text = 'MILL2-SPEED'::text) AND (dt > ((now() - '00:05:00'::interval))::timestamp without time zone))
Total runtime: 15706.179 ms
(4 rows)

> -----Original Message-----
> From: Michael Fuhr [mailto:mike(at)fuhr(dot)org]
> Sent: Wednesday, 17 November 2004 12:19
> To: Harvey, Allan AC
> Cc: Tom Lane
> Subject: Re: [GENERAL] table configuration tweak for performance gain.
>
>
> On Wed, Nov 17, 2004 at 09:39:11AM +1100, Harvey, Allan AC wrote:
>
> > The solution then was:-
> > an index of the right columns.
> > explicit, exact type casting ( I'm a casual ingres user, type
> > casting is something I never need or think you can do)
>
> PostgreSQL 8.0 will allow cross-type index usage, making the explicit
> cast unnecessary.
>
> > turning off enable_seqscan for specific queries seemed
> to help a bit too.
>
> Performance in general might improve if you address the planner's
> reasons for chosing an inefficient plan. Even though you're satisifed
> with performance now, would you mind posting the output of "EXPLAIN
> ANALYZE select ..." with enable_seqscan on and then with it off?
> If nothing else, an analysis might be educational for others.
>
> --
> Michael Fuhr
> http://www.fuhr.org/~mfuhr/
>