Re: Inconsistent query performance

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.
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'm currently using postgres 8.3.x

That was much more vulnerable to the write glut problem than
versions which are still in support.  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

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company