agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org, pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
Date: Tue, 8 Apr 2014 14:33:27 -0300 (ART)
Message-ID: <34678100.310095.1396978407846.JavaMail.root@fmed.uba.ar> (raw)
In-Reply-To: <23396.1396965001@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

Tom, thanks (as allways) for your answer. This is a 9.1.12. I have to say, im not very happy about if-elif-else'ing at all. 
The "conditional filter" es a pretty common pattern in our functions, i would have to add (and maintain) a substantial amount of extra code.

And i dont really understand why the optimizer issues, since the arguments are immutable "strings", and should (or could at least) be evaluated only once.

Thanks again for your time!

Gerardo



----- Mensaje original -----
> De: "Tom Lane" <tgl@sss.pgh.pa.us>
> Para: "Gerardo Herzig" <gherzig@fmed.uba.ar>
> CC: pgsql-performance@postgresql.org, "pgsql-sql" <pgsql-sql@postgresql.org>
> Enviados: Martes, 8 de Abril 2014 10:50:01
> Asunto: Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
> 
> Gerardo Herzig <gherzig@fmed.uba.ar> writes:
> > Hi all. I have a function that uses a "simple" select between 3
> > tables. There is a function argument to help choose how a WHERE
> > clause applies. This is the code section:
> > select * from....
> > [...]
> > where case $3
> >   when 'I' then [filter 1]
> >   when 'E' then [filter 2]
> >   when 'P' then [filter 3]
> > else true end
> 
> > When the function is called with, say, parameter $3 = 'I', the
> > funcion run in 250ms,
> > but when there is no case involved, and i call directly "with
> > [filter 1]" the function runs in 70ms.
> 
> > Looks like the CASE is doing something nasty.
> > Any hints about this?
> 
> Don't do it like that.  You're preventing the optimizer from
> understanding
> which filter applies.  Better to write three separate SQL commands
> surrounded by an if/then/else construct.
> 
> (BTW, what PG version is that?  I would think recent versions would
> realize that dynamically generating a plan each time would work
> around
> this.  Of course, that approach isn't all that cheap either.  You'd
> probably still be better off splitting it up manually.)
> 
> 			regards, tom lane
> 


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

view thread (4+ messages)  latest in thread

Message-ID: <34678100.310095.1396978407846.JavaMail.root@fmed.uba.ar>
Permalink:  ../34678100.310095.1396978407846.JavaMail.root@fmed.uba.ar/
Also on:    postgresql.org/message-id/34678100.310095.1396978407846.JavaMail.root@fmed.uba.ar

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-sql@postgresql.org
  Cc: gherzig@fmed.uba.ar, tgl@sss.pgh.pa.us
  Subject: Re: [PERFORM] performance drop when function argument is evaluated in WHERE clause
  In-Reply-To: <34678100.310095.1396978407846.JavaMail.root@fmed.uba.ar>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox