From: | "Good, Thomas" <tgood(at)svcmcny(dot)org> |
---|---|
To: | "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org> |
Subject: | Question about slow queries... |
Date: | 2010-05-21 15:44:29 |
Message-ID: | FA041D403EDFCD4397E1F6382360ECFCC2979966F3@SVCMCEMAIL.svcmc.svcmcny.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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 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 only happens on the 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 sister site 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=89 loops=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.988 rows=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
From | Date | Subject | |
---|---|---|---|
Next Message | Torsten Lange | 2010-05-21 16:22:24 | Re: Access Update error: solution. |
Previous Message | Little, Douglas | 2010-05-21 15:23:12 | Access Update error: solution. |