On Apr 8, 2013, at 7:09 PM, Kevin Grittner wrote: > Ramsey Gurley <rgurley@xxxxxxxxxxxxxxx> wrote: > >> I'm having issues with slow queries using postgres, and I'm >> finding some of the issues difficult to reproduce. My application >> logs slow queries for me, but often, when I go back to run explain >> analyze on the query it is very fast. I assume this is due to some >> sort of caching. > >> expression took 20147 ms: >> >> SELECT t0.appointment_id, t0.customer_id, t0.event_date, >> t0.patient_id, t0.transaction_id >> FROM customer.customer_transactions_detail t0 >> WHERE (t0.patient_id = 7441 AND t0.customer_id = 2965) >> >> "Index Scan using customer_id_patient_id_idx on customer_transactions_detail t0 >> (cost=0.00..10.22 rows=1 width=24) >> (actual time=35.952..99.487 rows=14 loops=1)" >> " Index Cond: ((customer_id = 2965) AND (patient_id = 7441))" >> "Total runtime: 99.537 ms" >> >> So it took 20 seconds at 12:18pm today, but now it takes ~100ms. > > Well, often when you get a faster run time when running a query > again it is due to caching, but there are other things which can > come into play. There could be blocking. There could be a glut of > disk writes at checkpoint time which holds up all other disk I/O. Is there a way to make checkpoints happen more frequently so that large ones don't cause two minute delays? > You could have had a badly bloated index when the query was run the > first time, and a VACUUM command or autovacuum cleaned things up > before your explain analyze. I did run a vacuum on the entire database the day before. I don't know if I have auto-vacuuming set up. > >> I'm currently using postgres 8.3.x > > That was much more vulnerable to the write glut problem than > versions which are still in support. I'm in the process of upgrading to 9.2.x. I'm also moving the database to a dedicated machine with more RAM available to the database. So maybe a less crufty setup in another week or two. > It's hard to even suggest > what steps to take next without knowing the OS, your hardware, or > your configuration. Please read these two pages: > > http://www.postgresql.org/support/versioning/ > > http://wiki.postgresql.org/wiki/SlowQueryQuestions Thanks for the suggestions Kevin :) > > -- > Kevin Grittner > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
