pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Mr Pink <mr_pink_is_the_only_pro@yahoo.com>
To: Scott Kirkwood <scottakirkwood@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Caching of Queries
Date: Thu, 23 Sep 2004 08:29:25 -0700 (PDT)
Message-ID: <20040923152925.18234.qmail@web41114.mail.yahoo.com> (raw)
In-Reply-To: <b3dc511704092212502a2ddb09@mail.gmail.com>

Not knowing anything about the internals of pg, I don't know how this relates, but in theory, 
query plan caching is not just about saving time re-planning queries, it's about scalability.
Optimizing queries requires shared locks on the database metadata, which, as I understand it
causes contention and serialization, which kills scalability. 

I read this thread from last to first, and I'm not sure if I missed something, but if pg isnt
caching plans, then I would say plan caching should be a top priority for future enhancements. It
needn't be complex either: if the SQL string is the same, and none of the tables involved in the
query have changed (in structure), then re-use the cached plan. Basically, DDL and updated
statistics would have to invalidate plans for affected tables. 

Preferably, it should work equally for prepared statements and those not pre-prepared. If you're
not using prepare (and bind variables) though, your plan caching down the drain anyway...

I don't think that re-optimizing based on values of bind variables is needed. It seems like it
could actually be counter-productive and difficult to asses it's impact.

That's the way I see it anyway.

:)

--- Scott Kirkwood <scottakirkwood@gmail.com> wrote:

> I couldn't find anything in the docs or in the mailing list on this,
> but it is something that Oracle appears to do as does MySQL.
> The idea, I believe, is to do a quick (hash) string lookup of the
> query and if it's exactly the same as another query that has been done
> recently to re-use the old parse tree.
> It should save the time of doing the parsing of the SQL and looking up
> the object in the system tables.
> It should probably go through the planner again because values passed
> as parameters may have changed. Although, for extra points it could
> look at the previous query plan as a hint.
> On the surface it looks like an easy enhancement, but what do I know?
> I suppose it would benefit mostly those programs that use a lot of
> PQexecParams() with simple queries where a greater percentage of the
> time is spent parsing the SQL rather than building the execute plan.
> What do you think?
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
> 



		
_______________________________
Do you Yahoo!?
Declare Yourself - Register online to vote today!
http://vote.yahoo.com



view thread (72+ messages)  latest in thread

Message-ID: <20040923152925.18234.qmail@web41114.mail.yahoo.com>
Permalink:  ../20040923152925.18234.qmail@web41114.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040923152925.18234.qmail@web41114.mail.yahoo.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: mr_pink_is_the_only_pro@yahoo.com, scottakirkwood@gmail.com
  Subject: Re: Caching of Queries
  In-Reply-To: <20040923152925.18234.qmail@web41114.mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox