X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DE71953661 for ; Mon, 18 Apr 2005 06:00:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12422-08 for ; Mon, 18 Apr 2005 09:00:41 +0000 (GMT) Received: from zproxy.gmail.com (zproxy.gmail.com [64.233.162.202]) by svr1.postgresql.org (Postfix) with ESMTP id 84328541C2 for ; Mon, 18 Apr 2005 06:00:36 -0300 (ADT) Received: by zproxy.gmail.com with SMTP id 34so1496520nzf for ; Mon, 18 Apr 2005 02:00:38 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=RxmpFDdn9Vz6YtECVwnW3llna+JZaLE5tPusEd75zGmekfD4Xrr3b3ToSs20eGOWNpauJvGGkzJ4jC+ZSTC4tqJz3/mMIZlSHkrEisWOO/ELSqsnoyQU318RmQxBvCzAqABxA3pbdQfmY5r882+/sgBi76N88DtIMQwGxcB9bv8= Received: by 10.36.8.17 with SMTP id 17mr349953nzh; Mon, 18 Apr 2005 02:00:38 -0700 (PDT) Received: by 10.36.22.15 with HTTP; Mon, 18 Apr 2005 02:00:38 -0700 (PDT) Message-ID: <758d5e7f05041802004ee56be4@mail.gmail.com> Date: Mon, 18 Apr 2005 11:00:38 +0200 From: Dawid Kuroczko Reply-To: Dawid Kuroczko To: pgsql-performance@postgresql.org Subject: Re: immutable functions vs. join for lookups ? In-Reply-To: <20050415205511.GB20345@nibiru.borg.metux.de> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <20050415205511.GB20345@nibiru.borg.metux.de> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.757 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, FROM_ENDS_IN_NUMS, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200504/389 X-Sequence-Number: 11786 On 4/15/05, Enrico Weigelt wrote: > a) SELECT items.a, items.b, ..., users.username FROM items, users > WHERE items.uid =3D users.uid; >=20 > c) CREATE FUNCTION id2username(oid) RETURNS text > LANGUAGE 'SQL' IMMUTABLE AS ' > SELECT username AS RESULT FROM users WHERE uid =3D $1'; You will be told that this function is not immutable but stable, and this is quite right. But consider such a function: CREATE OR REPLACE FUNCTION id2username (oid int) RETURNS TEXT AS $$ BEGIN IF oid =3D 0 THEN RETURN 'foo'; ELSIF oid =3D 1 THEN RETURN 'bar'; END IF; END; $$ LANGUAGE plpgsql IMMUTABLE; versus a lookup table with similar data. Logic suggests it should be faste= r than a table... It got me worried when I added: "RAISE WARNING 'Called'" after begin and I got lots of "Called" warnings when using this IMMUTABLE function in select... And the timings for ~6000 values in aaa table (and two values in lookup table) are: There is a query, output of the EXPLAIN ANALYZE, Time of EXPLAIN ANALYZE and "Real time" of SELECT (without EXPLAIN ANALYZE): a) simple select from temp table, and a lookup cost: EXPLAIN ANALYZE SELECT n FROM aaa; Seq Scan on aaa (cost=3D0.00..87.92 rows=3D5992 width=3D4) (actual time=3D0.011..24.849 rows=3D6144 loops=3D1) Total runtime: 51.881 ms (2 rows) Time: 52,882 ms Real time: 16,261 ms EXPLAIN ANALYZE SELECT id2username(n) FROM aaa limit 2; Limit (cost=3D0.00..0.03 rows=3D2 width=3D4) (actual time=3D0.111..0.150 rows=3D2 loops=3D1) -> Seq Scan on aaa (cost=3D0.00..104.80 rows=3D6144 width=3D4) (actual time=3D0.102..0.129 rows=3D2 loops=3D1) Total runtime: 0.224 ms (3 rows) Time: 1,308 ms Real time: 1,380 ms b) natural join with lookup table: EXPLAIN ANALYZE SELECT username FROM aaa NATURAL JOIN lookup; Hash Join (cost=3D2.45..155.09 rows=3D3476 width=3D32) (actual time=3D0.306..83.677 rows=3D6144 loops=3D1) Hash Cond: ("outer".n =3D "inner".n) -> Seq Scan on aaa (cost=3D0.00..87.92 rows=3D5992 width=3D4) (actual time=3D0.006..25.517 rows=3D6144 loops=3D1) -> Hash (cost=3D2.16..2.16 rows=3D116 width=3D36) (actual time=3D0.237..0.237 rows=3D0 loops=3D1) -> Seq Scan on lookup (cost=3D0.00..2.16 rows=3D116 width=3D36) (actual time=3D0.016..0.034 rows=3D2 loops=3D1) Total runtime: 107.378 ms (6 rows) Time: 109,040 ms Real time: 25,364 ms c) IMMUTABLE "static" lookup function: EXPLAIN ANALYZE SELECT id2username(n) FROM aaa; Seq Scan on aaa (cost=3D0.00..104.80 rows=3D6144 width=3D4) (actual time=3D0.089..116.397 rows=3D6144 loops=3D1) Total runtime: 143.800 ms (2 rows) Time: 144,869 ms Real time: 102,428 ms 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); Hash Left Join (cost=3D506.82..688.42 rows=3D6144 width=3D36) (actual time=3D102.382..182.661 rows=3D6144 loops=3D1) Hash Cond: ("outer".n =3D "inner".n) -> Seq Scan on aaa (cost=3D0.00..89.44 rows=3D6144 width=3D4) (actual time=3D0.012..24.360 rows=3D6144 loops=3D1) -> Hash (cost=3D506.82..506.82 rows=3D2 width=3D36) (actual time=3D102.217..102.217 rows=3D0 loops=3D1) -> Subquery Scan v_lookup (cost=3D476.05..506.82 rows=3D2 width=3D36) (actual time=3D53.626..102.057 rows=3D2 loops=3D1) -> Subquery Scan "values" (cost=3D476.05..506.80 rows=3D2 width=3D4) (actual time=3D53.613..102.023 rows=3D2 loops=3D1) -> Unique (cost=3D476.05..506.77 rows=3D2 width=3D4) (actual time=3D53.456..101.772 rows=3D2 loops=3D1) -> Sort (cost=3D476.05..491.41 rows=3D6144 width=3D4) (actual time=3D53.440..76.710 rows=3D6144 loops=3D1) Sort Key: n -> Seq Scan on aaa=20 (cost=3D0.00..89.44 rows=3D6144 width=3D4) (actual time=3D0.013..26.626 rows=3D6144 loops=3D1) Total runtime: 209.378 ms (11 rows) Time: 211,460 ms Real time: 46,682 ms ...so this IMMUTABLE is twice as slow (~100 ms) as the query joining itself with a SELECT DISTINCT on an IMMUTABLE function (~50 ms), which is twice as slow as JOIN against lookup table (~25 ms), and I feel this IMMUTABLE function could be around ~20 ms (~16 ms plus calling the function two times plus giving the values). Ah, and this is PostgreSQL 8.0.1 running under FreeBSD on a CPU: Intel(R) Celeron(R) CPU 2.40GHz (2400.10-MHz 686-class CPU). Regards, Dawid PS: I have a feeling that IMMUTABLE functions worked better in 7.4, yet I am unable to confirm this.