agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Gerardo Herzig <gherzig@fmed.uba.ar>
To: pgsql-performance@postgresql.org, "pgsql-sql " <pgsql-sql@postgresql.org>
Subject: performance drop when function argument is evaluated in WHERE clause
Date: Tue, 8 Apr 2014 08:53:41 -0300 (ART)
Message-ID: <731037499.293695.1396958021532.JavaMail.root@fmed.uba.ar> (raw)
In-Reply-To: <1321795206.293156.1396957278252.JavaMail.root@fmed.uba.ar>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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?

Thanks!
Gerardo



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

view thread (4+ messages)  latest in thread

Message-ID: <731037499.293695.1396958021532.JavaMail.root@fmed.uba.ar>
Permalink:  ../731037499.293695.1396958021532.JavaMail.root@fmed.uba.ar/
Also on:    postgresql.org/message-id/731037499.293695.1396958021532.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
  Subject: Re: performance drop when function argument is evaluated in WHERE clause
  In-Reply-To: <731037499.293695.1396958021532.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