X-Original-To: pgsql-hackers@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C50CC4763E7 for ; Sat, 1 Feb 2003 00:15:33 -0500 (EST) Received: from perrin.int.nxad.com (internal.ext.nxad.com [66.250.180.251]) by postgresql.org (Postfix) with ESMTP id 3AF56476160 for ; Sat, 1 Feb 2003 00:10:13 -0500 (EST) Received: by perrin.int.nxad.com (Postfix, from userid 1001) id E1F1621058; Fri, 31 Jan 2003 21:09:35 -0800 (PST) Date: Fri, 31 Jan 2003 21:09:35 -0800 From: Sean Chittenden To: Tom Lane Cc: Josh Berkus , Don Bowman , "'pgsql-hackers@postgresql.org'" Subject: Re: [PERFORM] not using index for select min(...) Message-ID: <20030201050935.GM15936@perrin.int.nxad.com> References: <200301311531.12605.josh@agliodbs.com> <20030201040954.GK15936@perrin.int.nxad.com> <4278.1044074132@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <4278.1044074132@sss.pgh.pa.us> User-Agent: Mutt/1.4i X-PGP-Key: finger seanc@FreeBSD.org X-PGP-Fingerprint: 6CEB 1B06 BFD3 70F6 95BE 7E4D 8E85 2E0A 5F5B 3ECB X-Web-Homepage: http://sean.chittenden.org/ X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/2 X-Sequence-Number: 34944 > > 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