pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Allan Kelly <allan.kelly@buildstore.co.uk>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Mystery: functions are slow with group by, but...
Date: Tue, 18 Apr 2000 14:02:02 -0400
Message-ID: <20624.956080922@sss.pgh.pa.us> (raw)
In-Reply-To: <38FC7C4D.7D8E1C12@buildstore.co.uk>
References: <38FC7C4D.7D8E1C12@buildstore.co.uk>

Allan Kelly <allan.kelly@buildstore.co.uk> writes:
> I have a big performance problem using a function:

> This takes 5-10 _minutes_ :
> select a4, total_live_a4_properties( a4 )
> from properties group by a4;

> There are 5132 records in the properties table, and 64 distinct a4 values.

> However this next one is < 3 _seconds_!
> select a4, count(*), (100*count(*)/total_live_a4_properties( a4 )) as percentage
> from properties group by a4;

> Can anyone tell me what's going on?

You were right to look to EXPLAIN for a hint about the problem, but
unfortunately EXPLAIN doesn't show you anything about where a function
call is evaluated in the plan tree.  If you dig into the EXPLAIN VERBOSE
output, you can see where it's evaluated.  It turns out that in the
first query, 6.5.* evaluates the function call in the seqscan's target
list, which means that the function is evaluated separately at each row
of the table :-(.  Then, all the duplicate rows are thrown away by the
group step, so you just did 5000+ useless function evaluations, each of
which needed a scan of the table.  In the second case, the behavior is
different because of the presence of an aggregate function --- the
planner knows it cannot evaluate that until the top level, so the whole
expression including the user function call is done at the output of the
aggregate step.  Net result, only one function evaluation per group.

This is, of course, pretty braindead.  It's fixed in 7.0: functions on
group columns are not evaluated until after the group step, with or
without aggregates.

			regards, tom lane



view thread (2+ messages)

Message-ID: <20624.956080922@sss.pgh.pa.us>
Permalink:  ../20624.956080922@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/20624.956080922@sss.pgh.pa.us

 · 

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-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, allan.kelly@buildstore.co.uk
  Subject: Re: Mystery: functions are slow with group by, but...
  In-Reply-To: <20624.956080922@sss.pgh.pa.us>
  References: <38FC7C4D.7D8E1C12@buildstore.co.uk> <20624.956080922@sss.pgh.pa.us>

* 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