Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0421E632D02 for ; Sat, 6 Mar 2010 10:51:14 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 84869-10 for ; Sat, 6 Mar 2010 14:51:03 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f228.google.com (mail-bw0-f228.google.com [209.85.218.228]) by mail.postgresql.org (Postfix) with ESMTP id 459F4632A32 for ; Sat, 6 Mar 2010 10:51:03 -0400 (AST) Received: by bwz28 with SMTP id 28so1479505bwz.34 for ; Sat, 06 Mar 2010 06:51:01 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:sender:received:in-reply-to :references:date:x-google-sender-auth:message-id:subject:from:to:cc :content-type; bh=0CvMbv/uOtePglqDWiK5sOqAKsIQXyiOxBbrKM4K9zU=; b=rtY+h8cL84Embc9XcRiJhSuBp0n5aEcGfdyfOVQd8EZDg5pjxfAOrirCdU/V8CnztB +ZKLoMidSTKefiWXoJhAARG1yXgakAwTaTkVQ4C5HmGqVNggwcdpq+P7LyYIMgnS81iR 2rVUAKAvW4dEoBQZJ+MvGj9S9eOrUTIQBSViI= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:sender:in-reply-to:references:date :x-google-sender-auth:message-id:subject:from:to:cc:content-type; b=akZXj71zHzP/hKkrNQlO32VUeQDBY+xj+FJ1rG2jsba4IbWXC7YwnwQdY9YYbg8Pkc DcO36h+k4TYuRvkTeTlIWzRsq18L5P7ocyb9hWlJ8qL/nHmPLvPRkfmBrDs09lhV5Lz/ 0iGTNr2ok45XBrk8G7NWRul3WkghCBNREvyag= MIME-Version: 1.0 Received: by 10.204.49.78 with SMTP id u14mr1782928bkf.147.1267886739000; Sat, 06 Mar 2010 06:45:39 -0800 (PST) In-Reply-To: <4B91C184.4090906@venaver.info> References: <4B91C184.4090906@venaver.info> Date: Sat, 6 Mar 2010 14:45:38 +0000 X-Google-Sender-Auth: d9e9236e9619ae73 Message-ID: <407d949e1003060645i408e0648te67dfd722172f76a@mail.gmail.com> Subject: Re: Does IMMUTABLE property propagate? From: Greg Stark To: Petru Ghita Cc: pgsql-sql mailing list Content-Type: multipart/alternative; boundary=00032555b1f6b18d5e048122e2f8 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/43 X-Sequence-Number: 33750 --00032555b1f6b18d5e048122e2f8 Content-Type: text/plain; charset=ISO-8859-1 The immutable property had nothing to do with caching results. Postgres never caches the results of functions. The immutable property is used top determine if it's safe to use indexes or other plans that avoid evaluating an expression repeatedly. On 6 Mar 2010 02:45, "Petru Ghita" wrote: -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as IMMUTABLE, does the query planner cache the result of f3 and reuse it or if you want to get a little more speed you better explicitly define yourself f3 as IMMUTABLE? I had an aggregate query like: select id, sum(p1*f1(a)/f2(b) as r1, sum(p2*f1(a)/f2(b) as r2, ... sum(pn*f1(a)/f2(b) as rn ... group by id; Where f1(x) and f2(x) were defined as IMMUTABLE. By the experiments I ran looks like after defining a new function f3(a,b):= f1(a)/f2(b) and rewriting the query as: select id, sum(p1*f3(a,b) as r1, sum(p2*f3(a,b) as r2, ... sum(pn*f3(a,b) as rn ... group by id; *Looks like* I got a little (5%) improvement in performance of the query. Is there a way to find out if the function is re-evaluated each time? Is this the recommended way to proceed? Thank you! Petru Ghita -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkuRwYQACgkQt6IL6XzynQTHEgCffi2QMWkkvTIsuglsanvcUyRB I+wAoKr22B7FJJVDCssGKGwB8zr4NjQG =V/BS -----END PGP SIGNATURE----- -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql --00032555b1f6b18d5e048122e2f8 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable

The immutable property had nothing to do with caching results. Postgres = never caches the results of functions. The immutable property is used top d= etermine if it's safe to use indexes or other plans that avoid evaluati= ng an expression repeatedly.

On 6 Mar 2010 02:45, "Petru Ghita" &= lt;petrutz@venaver.info> wro= te:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as
IMMUTABLE, does the query planner cache the result of f3 and reuse it
or if you want to get a little more speed you better explicitly define
yourself f3 as IMMUTABLE?

I had an aggregate query like:

select id,
=A0 =A0 =A0 sum(p1*f1(a)/f2(b) as r1,
=A0 =A0 =A0 sum(p2*f1(a)/f2(b) as r2,
=A0 =A0 =A0 ...
=A0 =A0 =A0 sum(pn*f1(a)/f2(b) as rn

...
group by id;

Where f1(x) and f2(x) were defined as IMMUTABLE.

By the experiments I ran looks like after defining a new function
f3(a,b):=3D f1(a)/f2(b) and rewriting the query as:

select id,
=A0 =A0 =A0 sum(p1*f3(a,b) as r1,
=A0 =A0 =A0 sum(p2*f3(a,b) as r2,
=A0 =A0 =A0 ...
=A0 =A0 =A0 sum(pn*f3(a,b) as rn

...
group by id;

*Looks like* I got a little (5%) improvement in performance of the
query. Is there a way to find out if the function is re-evaluated each
time?
Is this the recommended way to proceed?

Thank you!

Petru Ghita
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkuRwYQACgkQt6IL6XzynQTHEgCffi2QMWkkvTIsuglsanvcUyRB
I+wAoKr22B7FJJVDCssGKGwB8zr4NjQG
=3DV/BS
-----END PGP SIGNATURE-----


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
= http://www.postgresql.org/mailpref/pgsql-sql

--00032555b1f6b18d5e048122e2f8--