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
withouttime 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
withouttime zone)) 
 Total runtime: 15706.179 ms
(4 rows)




> -----Original Message-----
> From: Michael Fuhr [mailto:mike@fuhr.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/
>