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
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