Re: Cached Query Plans (was: global prepared statements)

On Sat, Apr 12, 2008 at 2:44 PM, Perez <arturo(at)ethicist(dot)net> wrote:
> In article <20080411170609(dot)GB4392(at)alvh(dot)no-ip(dot)org>,
>
> > PFC wrote:
> >
> > > So, where to go from that ? I don't see a way to implement this without
> > > a (backwards-compatible) change to the wire protocol, because the clients
> > > will want to specify when a plan should be cached or not. Since the user
> > > should not have to name each and every one of the statements they want to
> > > use plan caching, I see the following choices :
>
>
> Doesn't Oracle do this now transparently to clients? That, I believe
> Oracle keeps a statement/plan cache in its shared memory segment (SGA)
> that greatly improves its performance at running queries that don't
> change very often.
>
> From that point of view, Oracle at least sees benefits in doing this.
> From my POV a transparent performance enhancer for all those PHP and
> Rails apps out there.

There are other benefits as well. Oracle lets you see the statistics associated
with given plans. So you can see how many times given (cached) query was
executed, how much resources did it consume and do on.

Right now the only way of getting such information from PostgreSQL is by
logging all queries and analyzing logs. The current_query column of
pg_stat_activity is useless as the (prepared) queries are usually so short
lived that you will see one execution out of thousands happening.

Nooow, suppose we do have cached plans. Then we can have a view
pg_stat_queries + a stats collector which will track number of executions,
number of blocks hit, blocks read, etc. Would be great! :)

Regards,
Dawid