Skip site navigation (1) Skip section navigation (2)

Re: Slow performance...

From: "PGMailList" <pgmail(at)pgexplorer(dot)com>
To: "wim" <wdh(at)belbone(dot)be>, <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Slow performance...
Date: 2002-03-11 12:05:33
Message-ID: 000701c1c8f5$0c248ff0$05faa8c0@edios (view raw or flat)
Thread:
Lists: pgsql-novice
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)
>


In response to

Responses

pgsql-novice by date

Next:From: Josh BerkusDate: 2002-03-11 16:49:20
Subject: Re: Slow performance...
Previous:From: Nicolas BoretosDate: 2002-03-11 11:14:43
Subject: Sparc/Solaris2.6 Postgresql 7.1.3 Binary?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group