Re: Slow performance...
От | Hano de la Rouviere |
---|---|
Тема | Re: Slow performance... |
Дата | |
Msg-id | 001a01c1c922$72a87c00$c80ba8c0@sabex.com обсуждение исходный текст |
Ответ на | Slow performance... (wim <wdh@belbone.be>) |
Список | pgsql-novice |
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@belbone.be> To: "PGMailList" <pgmail@pgexplorer.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@belbone.be> > > To: <pgsql-novice@postgresql.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@belbone.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@postgresql.org) > >> > >> > > > > > > > -- > Kind regards, > > Wim De Hul > Belgacom Belbone > -------------------------------- > Mail : wdh@belbone.be > Mobile : +32 479 952004 > Ripe : WDH25-RIPE > Registered Linux User: #260015 > --------------------------------
В списке pgsql-novice по дате отправления: