Search Postgresql Archives

Re: Inconsistent query performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux