Re: Transient plans versus the SPI API

On 08/07/2011 12:25 PM, Hannu Krosing wrote:
> On Sun, 2011-08-07 at 11:15 +0200, Hannu Krosing wrote:
>> On Wed, 2011-08-03 at 15:19 -0400, Tom Lane wrote:
>>> Hm, you mean reverse-engineering the parameterization of the query?
>> Yes, basically re-generate the query after (or while) parsing, replacing
>> constants and arguments with another set of generated arguments and
>> printing the list of these arguments at the end. It may be easiest to do
>> This in parallel with parsing.
>>
>>> Interesting thought, but I really don't see a way to make it practical.
>> Another place where this could be really useful is logging& monitoring
>>
>> If there were an option to log the above queries as
>>
>> "SELECT a, b FROM foo WHERE c = $1", (123)
>> "SELECT a, b FROM foo WHERE c = $1", (97)
>> "SELECT a, b FROM foo WHERE c = $1", (236)
> The main monitoring use_case would be pg_stat_statements,
> http://developer.postgresql.org/pgdocs/postgres/pgstatstatements.html
> which is currently pretty useless for queries without parameters

I was trying to implement something similar for pgpool-II. The user
could configure queries for which cached plans are wanted. The
configuration would have been a file containing lines in format "SELECT
* FROM foo WHERE id = ?". I did not get anything implemented, as there
were some problems. The problems were mainly with DEALLOCATE ALL called
without pgpool-II knowing it, issues with search_path and the amount of
work needed to implement parse tree matching.

It would be interesting if pg_stat_statements would be globally
available with queries using generic arguments. First, there would be an
obvious heuristic for when to cache the plan: If the average runtime of
the query is much larger than the average planning time, there is no
point in caching the plan. This would also give one option for cache hit
estimation. The hit_percent is directly available. On the other hand
pg_stat_statements could easily become a choke-point.

I would love to work on this, but I lack the needed skills. Maybe I
could take another try for writing a proof-of-concept parse tree
transformer and matcher, but I doubt I can produce anything useful.

- Anssi