On 4/18/05, Merlin Moncure <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly