Date: Thu, 27 May 2010 16:09:23 +0200
From: "A. Kretschmer" <andreas.kretschmer@schollglas.com>
To: pgsql-sql@postgresql.org
Subject: Re: Question about slow queries...
Message-ID: <20100527140923.GF6907@a-kretschmer.de>
References: 
 <FA041D403EDFCD4397E1F6382360ECFCC2979966F3@SVCMCEMAIL.svcmc.svcmcny.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <FA041D403EDFCD4397E1F6382360ECFCC2979966F3@SVCMCEMAIL.svcmc.svcmcny.org>
User-Agent: Mutt/1.5.9i

In response to Good, Thomas :
> 
> Hi, 
> 
> I have a question about a query that starts out fine and over time
> slows to a halt - but only on a webhosted site.  Locally it does fine.
> 
> The query is a singleton select (no joins), hitting a table with about
> 5,000 records in it. Over time the query slows to a crawl and I have

Okay, about 5000 records.


> to dump and rebuild/repopulate to restore efficiency. Vacuum does
> nothing but a dump and reload fixes the problem and the query runs
> lightning fast again -- for a period of a week or so. As stated this

okay

> 
> With everything working properly (table rebuilt just yesterday) explain analyze produces this on the webhost:
> 
>  Sort  (cost=121547.89..121558.43 rows=4214 width=4620) (actual time=386.172..386.224 rows=89 loops=1)
>    Sort Key: visit_date
>    ->  Seq Scan on client_service_note  (cost=0.00..100334.19 rows=4214 width=4620) (actual time=0.019..385.917 rows=89 loops=1)
>          Filter: (client_id = 385)
>  Total runtime: 386.335 ms

Well, estimated rows= 4214, nealy the whole table.Because of this the
planner choose a seq-scan. But - real only 89 rows with this condition.

Again: estimated rows=4214, real rows=89. This is a big difference!

I think, you have a lot of dead tuples in this table - many updates
and/or deletes & inserts. You should check your vacuum-strategy.

Other reason, maybe, wrong statistics. You can try to increase
statistics target. (alter table ... ALTER [ COLUMN ] column SET
STATISTICS 100 or more)


> 
> And locally:
> 
>  Sort  (cost=535.82..536.04 rows=88 width=696) (actual time=2.140..2.156 rows=88 loops=1)
>    Sort Key: visit_date
>    Sort Method:  quicksort  Memory: 65kB
>    ->  Seq Scan on client_service_note  (cost=0.00..532.98 rows=88 width=696) (actual time=0.014..1.988 rows=88 loops=1)
>          Filter: (client_id = 385)
>  Total runtime: 2.295 ms

Do you have an INDEX on client_id?


> 
> My query is terse:
> SELECT * FROM client_service_note WHERE client_id = 385 ORDER BY visit_date DESC;
> 
> Locally I'm running 8.4.2, the webhost is 8.1.18

There are many improvements between 8.1 and 8.4 ...


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99