Re: Inconsistent query performance

On Apr 8, 2013, at 7:09 PM, Kevin Grittner wrote:

> Ramsey Gurley <rgurley(at)smarthealth(dot)com> 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