pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Dawid Kuroczko <qnex42@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: immutable functions vs. join for lookups ?
Date: Mon, 18 Apr 2005 16:19:37 +0200
Message-ID: <758d5e7f05041807194a78b55a@mail.gmail.com> (raw)
In-Reply-To: <6EE64EF3AB31D5448D0007DD34EEB3415C2596@Herge.rcsinc.local>
References: <6EE64EF3AB31D5448D0007DD34EEB3415C2596@Herge.rcsinc.local>

On 4/18/05, Merlin Moncure <merlin.moncure@rcsonline.com> wrote:
> > d) self-join with a function ;)
> >   EXPLAIN ANALYZE SELECT * FROM (SELECT n, id2username(n) AS username
> > FROM (SELECT DISTINCT n FROM aaa) AS values) AS v_lookup RIGHT JOIN
> > aaa USING (n);
> 
> That's pretty clever.
> It sure seems like the server was not caching the results of the
> function...maybe the server thought it was to small a table to bother?

Nah, I don't thinks so.  Having around 2 097 152 rows of 1s and 0s takes
48 seconds for id2username() query.
The "self join" you've quoted above takes 32 seconds.
SELECT n FROM aaa; takes 7 seconds.

Thinking further...
SELECT CASE n WHEN 0 THEN 'foo' WHEN 1 THEN 'bar' END FROM aaa;
takes 9 seconds.

CREATE OR REPLACE FUNCTION id2un_case(oid int) RETURNS text AS $$
BEGIN RETURN CASE oid WHEN 0 THEN 'foo' WHEN 1 THEN 'bar' END; END; $$
LANGUAGE plpgsql IMMUTABLE;
SELECT id2un_case(n) FROM aaa;
...takes 36 seconds

...and to see how it depends on flags used:
SELECT count(id2un_case(n)) FROM aaa;
...id2un_case(n) IMMUTABLE takes 29900,114 ms
...id2un_case(n) IMMUTABLE STRICT takes 30187,958 ms
...id2un_case(n) STABLE takes 31457,560 ms
...id2un_case(n) takes 33545,178 ms
...id2un_case(n) VOLATILE takes 35150,920 ms
(and a count(CASE n WHEN ... END) FROM aaa takes: 2564,188 ms


I understand that these measurements are not too accurate.  They
were done on idle system, and the queries were run couple of times
(to make sure they're cached :)).  I believe either something is minor
performance difference between IMMUTABLE STABLE and even
VOLATILE plpgsql... :(

Oh, and doing things like "ORDER BY n" or "WHERE n = 1" didn't help
either...

I still wonder whether it's only my case or is there really something
wrong with these functions?

   Regards,
       Dawid



view thread (18+ messages)  latest in thread

Message-ID: <758d5e7f05041807194a78b55a@mail.gmail.com>
Permalink:  ../758d5e7f05041807194a78b55a@mail.gmail.com/
Also on:    postgresql.org/message-id/758d5e7f05041807194a78b55a@mail.gmail.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: qnex42@gmail.com
  Subject: Re: immutable functions vs. join for lookups ?
  In-Reply-To: <758d5e7f05041807194a78b55a@mail.gmail.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