pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sean Chittenden <sean@chittenden.org>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: Don Bowman <don@sandvine.com>
Cc: 'pgsql-hackers@postgresql.org' <pgsql-hackers@postgresql.org>
Subject: Re: [PERFORM] not using index for select min(...)
Date: Fri, 31 Jan 2003 21:09:35 -0800
Message-ID: <20030201050935.GM15936@perrin.int.nxad.com> (raw)
In-Reply-To: <4278.1044074132@sss.pgh.pa.us>
References: <FE045D4D9F7AED4CBFF1B3B813C8533701023616@mail.sandvine.com>
	<200301311531.12605.josh@agliodbs.com>
	<20030201040954.GK15936@perrin.int.nxad.com>
	<4278.1044074132@sss.pgh.pa.us>

> > Now, there are some obvious problems:
> 
> You missed the real reason why this will never happen: it completely
> kills any prospect of concurrent updates.  If transaction A has
> issued an update on some row, and gone and modified the relevant
> aggregate cache entries, what happens when transaction B wants to
> update another row?  It has to wait for A to commit or not, so it
> knows whether to believe A's changes to the aggregate cache entries.

I never claimed it was perfect, :) but it'd be is no worse than a
table lock.  For the types of applications that this would be of
biggest use to, there would likely be more reads than writes and it
wouldn't be as bad as one could imagine.  A few examples:

# No contension
Transaction A begins
Transaction A updates tab1
Transaction B begins
Transaction B updates tab1
Transaction B commits
Transaction A commits

# contension
Transaction A begins
Transaction A updates tab1
Transaction B begins
Transaction B updates tab1
Transaction A commits
Transaction B commits

This is just about the only case that I can see where there would be
contension.  In this case, transaction B would have to re-run its
trigger serially.  In the worse case scenario:

Transaction A begins
Transaction A updates tab1
Transaction B begins
Transaction B updates tab1
Transaction A commits
Transaction B selects
Transaction B updates tab1 again
Transaction B commits

In my journals or books I haven't found any examples of a transaction
based cache that'd work any better than this.  It ain't perfect, but,
AFAICT, it's as good as it's going to get.  The only thing that I
could think of that would add some efficiency in this case would be to
have transaction B read trough the committed changes from a log file.
After a threshold, it could be more efficient than having transaction
B re-run its queries.

Like I said, it ain't perfect, but what would be a better solution?
::shrug:: Even OODB's with stats agents have this problem (though
their overhead for doing this kind of work is much much lower).  -sc

-- 
Sean Chittenden



view thread (12+ messages)  latest in thread

Message-ID: <20030201050935.GM15936@perrin.int.nxad.com>
Permalink:  ../20030201050935.GM15936@perrin.int.nxad.com/
Also on:    postgresql.org/message-id/20030201050935.GM15936@perrin.int.nxad.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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: sean@chittenden.org, tgl@sss.pgh.pa.us, josh@agliodbs.com, don@sandvine.com, pgsql-hackers@postgresql.org
  Subject: Re: [PERFORM] not using index for select min(...)
  In-Reply-To: <20030201050935.GM15936@perrin.int.nxad.com>
  References: <FE045D4D9F7AED4CBFF1B3B813C8533701023616@mail.sandvine.com> <200301311531.12605.josh@agliodbs.com> <20030201040954.GK15936@perrin.int.nxad.com> <4278.1044074132@sss.pgh.pa.us> <20030201050935.GM15936@perrin.int.nxad.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