pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Aaron Werman <awerman2@hotmail.com>
To: Iain <iain@mst.co.jp>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jim C. Nasby <decibel@decibel.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Caching of Queries
Date: Tue, 28 Sep 2004 09:04:34 -0400
Message-ID: <BAY18-DAV126nx8dyDd000801d8@hotmail.com> (raw)
References: <b3dc511704092212502a2ddb09@mail.gmail.com>
	<20040923152925.18234.qmail@web41114.mail.yahoo.com>
	<20040927191836.GS1297@decibel.org>
	<00b201c4a4ff$bd73b8b0$7201a8c0@mst1x5r347kymb>
	<9617.1096341460@sss.pgh.pa.us>


----- Original Message ----- 
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Iain" <iain@mst.co.jp>
Cc: "Jim C. Nasby" <decibel@decibel.org>; <pgsql-performance@postgresql.org>
Sent: Monday, September 27, 2004 11:17 PM
Subject: Re: [PERFORM] Caching of Queries


> "Iain" <iain@mst.co.jp> writes:
> > I can only tell you (roughly) how it works wth Oracle,
>
> Which unfortunately has little to do with how it works with Postgres.
> This "latches" stuff is irrelevant to us.

Latches are the Oracle term for semaphores. Both Oracle and pg use
semaphores and spin locks to serialize activity in critical sections. I
believe that the point that blocking/queuing reduces scalability is valid.

>
> In practice, any repetitive planning in PG is going to be consulting
> catalog rows that it draws from the backend's local catalog caches.
> After the first read of a given catalog row, the backend won't need
> to re-read it unless the associated table has a schema update.  (There
> are some other cases, like a VACUUM FULL of the catalog the rows came
> from, but in practice catalog cache entries don't change often in most
> scenarios.)  We need place only one lock per table referenced in order
> to interlock against schema updates; not one per catalog row used.
>
> The upshot of all this is that any sort of shared plan cache is going to
> create substantially more contention than exists now --- and that's not
> even counting the costs of managing the cache, ie deciding when to throw
> away entries.

I imagine a design where a shared plan cache would consist of the plans,
indexed by a statement hash and again by dependant objects.  A statement to
be planned would be hashed and matched to the cache. DDL would need to
synchronously destroy all dependant plans. If each plan maintains a validity
flag, changing the cache wouldn't have to block so I don't see where there
would be contention.

>
> A backend-local plan cache would avoid the contention issues, but would
> of course not allow amortizing planning costs across multiple backends.
>
> I'm personally dubious that sharing planning costs is a big deal.
> Simple queries generally don't take that long to plan.  Complicated
> queries do, but I think the reusability odds go down with increasing
> query complexity.
>

I think both the parse and planning are major tasks if the transaction rate
is high. Simple queries can easily take much longer to plan than execute, so
this is a scalability concern. Caching complicated queries is valuable -
apps seem to have lots of similar queries because they are intimately
related to the data model.

> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>



view thread (72+ messages)  latest in thread

Message-ID: <BAY18-DAV126nx8dyDd000801d8@hotmail.com>
Permalink:  ../BAY18-DAV126nx8dyDd000801d8@hotmail.com/
Also on:    postgresql.org/message-id/BAY18-DAV126nx8dyDd000801d8@hotmail.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: awerman2@hotmail.com, iain@mst.co.jp, tgl@sss.pgh.pa.us, decibel@decibel.org
  Subject: Re: Caching of Queries
  In-Reply-To: <BAY18-DAV126nx8dyDd000801d8@hotmail.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