From: | Andomar <andomar(at)aule(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Monitoring query plan cache |
Date: | 2014-12-20 12:00:36 |
Message-ID: | 549564E4.4060800@aule.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
We run a Postgres 9.1 system that handles a lot of identical queries
(around 500 per second.) The queries originate from a Python WSGI script
running under Apache.
Performance is an issue, and we suspect query planning might be an
underlying cause. I've rewritten the scripts from ad-hoc SQL to a
Postgres functions (CREATE FUNCTION) and we saw server load go down
quite a bit.
Now we'd like to monitor the Postgres query parser, to confirm that it's
not analyzing the same SQL over and over, and see if we missed other things.
a) Is it possible that Postgres caches the query plans for psql
functions, but not for ad-hoc SQL?
b) Is there a way to see if a single query uses a cached execution plan?
c) Can you monitor the query parser as a whole, with stats like
parses/sec or cache hits/sec?
d) Is there a way to montior how many reads are done against the
statistics table (pg_statistics)?
Thanks for any answers or pointers,
Andomar
P.S. I've asked this question on StackExchange
http://dba.stackexchange.com/questions/86620/monitoring-the-postgres-query-parser/86626#86626
but I'm looking for more specific information.
From | Date | Subject | |
---|---|---|---|
Next Message | Pierre | 2014-12-20 14:32:23 | pg_audit_users - Auditing user activity |
Previous Message | Joseph Kregloh | 2014-12-20 08:42:34 | Re: Blocking access by remote users for a specific time period |