pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Christopher Browne <cbbrowne@acm.org>
To: pgsql-performance@postgresql.org
Subject: Re: slow plan for min/max
Date: Mon, 08 Sep 2003 17:41:43 -0400
Message-ID: <m3d6ebvtyg.fsf@chvatal.cbbrowne.com> (raw)
References: <1063046951.9051.68.camel@tokyo>
	<Pine.LNX.4.33.0309081434150.11709-100000@css120.ihs.com>

After takin a swig o' Arrakan spice grog, scott.marlowe@ihs.com ("scott.marlowe") belched out...:
> On Mon, 8 Sep 2003, Neil Conway wrote:
>> On Mon, 2003-09-08 at 11:56, scott.marlowe wrote:
>> > Basically, Postgresql uses an MVCC locking system that makes massively 
>> > parallel operation possible, but costs in certain areas, and one of those 
>> > areas is aggregate performance over large sets.  MVCC makes it very hard 
>> > to optimize all but the simplest of aggregates, and even those 
>> > optimzations which are possible would wind up being quite ugly at the 
>> > parser level.
>> 
>> As was pointed out in a thread a couple days ago, MIN/MAX() optimization
>> has absolutely nothing to do with MVCC. It does, however, make
>> optimizing COUNT() more difficult.
>
> Not exactly.  While max(id) is easily optimized by query replacement, 
> more complex aggregates will still have perfomance issues that would not 
> be present in a row locking database.  i.e. max((field1/field2)*field3) is 
> still going to cost more to process, isn't it?

That sort of MAX() would be difficult to optimize in almost any case,
and would mandate doing a scan across the relevant portion of the
table...

... Unless you had a functional index on (field1/field2)*field3, in
which case it might well be that this would cost Still Less.

I still can't fathom what this has to do with MVCC; you have yet to
actually connect it with that...
-- 
let name="cbbrowne" and tld="ntlug.org" in String.concat "@" [name;tld];;
http://www3.sympatico.ca/cbbrowne/lsf.html
"Cars  move  huge weights at   high    speeds by controlling   violent
explosions many times a second.  ...car analogies are always fatal..."
-- <westprog@my-dejanews.com>



view thread (20+ messages)  latest in thread

Message-ID: <m3d6ebvtyg.fsf@chvatal.cbbrowne.com>
Permalink:  ../m3d6ebvtyg.fsf@chvatal.cbbrowne.com/
Also on:    postgresql.org/message-id/m3d6ebvtyg.fsf@chvatal.cbbrowne.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: cbbrowne@acm.org
  Subject: Re: slow plan for min/max
  In-Reply-To: <m3d6ebvtyg.fsf@chvatal.cbbrowne.com>
  References: <1063046951.9051.68.camel@tokyo> <Pine.LNX.4.33.0309081434150.11709-100000@css120.ihs.com> <m3d6ebvtyg.fsf@chvatal.cbbrowne.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