Thread: Question about slow queries...
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. Locallyit 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 toa crawl and I have to dump and rebuild/repopulate to restore efficiency. Vacuum does nothing but a dump and reload fixesthe problem and the query runs lightning fast again -- for a period of a week or so. As stated this only happens onthe webhosted site (bluehost.com). Locally I have the same table with 118,000 records in it and it runs very fast. (Related:even a "SELECT COUNT(*) on the the target table takes forever when the lethargy has set in). The only thing that might be a factor that I can see is that my ORDER BY uses an unindexed date - however, we have a sistersite on the same webhost (diff server) and it does not experience slowdowns. 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=89loops=1) Filter: (client_id = 385)Total runtime: 386.335 ms 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.988rows=88 loops=1) Filter: (client_id = 385)Total runtime: 2.295 ms 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 Anyone have any thoughts on what I'm not seeing?? thanks, Tom -- Thomas Good, Senior Database Administrator Residential Services, Behavioral Health Services Bayley Seton Campus, SVCMCNY 75 Vanderbilt Avenue, Room 5-47 Staten Island, NY 10304 718.818.5528
"Good, Thomas" <tgood@svcmcny.org> writes:
> I have a question about a query that starts out fine and over time slows to a halt - but only on a webhosted site.
Locallyit 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 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.
If a dump and reload fixes it then you have table-bloat or index-bloat
problems. A simple VACUUM won't fix that once it's happened. You need
to VACUUM more often so that it doesn't get bloated in the first place.
> Locally I'm running 8.4.2, the webhost is 8.1.18
... and the reason for the difference is probably that 8.4.x has a
reasonably decent autovacuum facility, which 8.1.x does not. Suggest
bugging your webhost provider to provide a less obsolete version of
Postgres.
regards, tom lane
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=89loops=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