Monitoring query plan cache

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.

Responses

Browse pgsql-general by date

  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