Re: Hope for a new PostgreSQL era?

On 12/10/2011 09:28 PM, Craig Ringer wrote:
> One thing I think would be interesting for this would be to identify
> slow queries (without doing detailed plan timing) and flag them for
> more detailed timing if they're run again within <x> time. I suspect
> this would only be practical with parameterised prepared statements
> where the query string remained the same, but that'd still be interesting

There are actually two patches sitting in the current PostgreSQL
CommitFest that allow normalizing query strings in a way that they could
be handled like this even if not prepared, as part of
pg_stat_statements.  What you're asking for is basically a hybrid of
that and auto_explain, with something smarter deciding when the explain
is triggered.  Interesting idea, I hadn't thought of that heuristic
before.  It won't be hard to do if the query normalization stuff commits.

> Personally I'd choose good performance monitoring over user/query
> priorities any day. With good perf monitoring I can script from the
> outside I have a lot more control, can send alerts, etc etc.

Luckily for you it's hard to do it in any other order.  When I think
about how we'd have to validate whether query prioritization code was
operating as expected or not, I imagine some extra monitoring tools
really need to get built first.  Might as well expose those for people
like yourself too, once they're built for that purpose.

--
Greg Smith   2ndQuadrant US    greg@2ndQuadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support  www.2ndQuadrant.us