Re: Slow performance...

Its in the way postgres utilize the index.
Try Explain Verbose Select and you will see why.

http://www.pgexplorer.com
PostgreSQL GUI

----- Original Message -----
From: "wim" <wdh(at)belbone(dot)be>
To: "PGMailList" <pgmail(at)pgexplorer(dot)com>
Sent: Monday, March 11, 2002 2:55 PM
Subject: Re: [NOVICE] Slow performance...

> Hey...
>
> The first topic helped me a lot, but can explain someone why?
>
>
> Thanx!
>
>
> PGMailList wrote:
>
> > 1)
> > Replace
> > WHERE timestamp
> >
> >>LIKE '$yr-$mon-$day $hour:$minute%'
> >>
> >
> > with
> > $minute2 = $minute +1;
> > timestamp > '$yr-$mon-$day $hour:$minute' and
> > timestamp <'$yr-$mon-$day $hour:$minute2'
> >
> > AND
> > 2)
> >
> > Do You Realy need the % infront of %$billingtable%'
> > if not it will inprove your speed drastically.
> >
> > 3)
> > DO a explain verbose select .... to make postgres sure its using your
index.
> >
> >
> > http://www.pgexplorer.com
> > GUI Tool for PostgreSQL
> >
> > ----- Original Message -----
> > From: "wim" <wdh(at)belbone(dot)be>
> > To: <pgsql-novice(at)postgresql(dot)org>
> > Sent: Monday, March 11, 2002 1:12 PM
> > Subject: [NOVICE] Slow performance...
> >
> >
> >
> >>Hey all,
> >>
> >>I encounter a major problem: I've running Postgres 7.1 on a Sun 220R
> >>(with only one processor). Postgres has a database with a table with
> >>more than 300000 rows. The lay-out of my table is:
> >>
> >>timestamp | interface | datain | dataout
> >>
> >>I created indexes on the first two fields (timestamp and interface).
> >>
> >>
> >>I've written a perl script that fetches data from the DB.
> >>I do it the following way:
> >>
> >>SELECT SUM(datain), SUM(dataout) FROM traffic_counters WHERE timestamp
> >>LIKE '$yr-$mon-$day $hour:$minute%' AND interface LIKE
'%$billingtable%'";
> >>
> >>Now the problem is that it takes about ten seconds to get only a single
> >>result... There are 24 hours in one day, so you can see that my script
> >>runs for quite a time...
> >>
> >>Is there a way to speed up thing (a lot)?
> >>
> >>Thanx guys!
> >>
> >>
> >>--
> >>Kind regards,
> >>
> >>Wim De Hul
> >>Belgacom Belbone
> >>--------------------------------
> >> Mail : wdh(at)belbone(dot)be
> >> Mobile : +32 479 952004
> >> Ripe : WDH25-RIPE
> >> Registered Linux User: #260015
> >>--------------------------------
> >>
> >>
> >>---------------------------(end of broadcast)---------------------------
> >>TIP 2: you can get off all lists at once with the unregister command
> >> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
> >>
> >>
> >
> >
>
>
> --
> Kind regards,
>
> Wim De Hul
> Belgacom Belbone
> --------------------------------
> Mail : wdh(at)belbone(dot)be
> Mobile : +32 479 952004
> Ripe : WDH25-RIPE
> Registered Linux User: #260015
> --------------------------------