From: Jim C. Nasby <jim@nasby.net>
To: Mara Dalponte <dalponte@sol.info.unlp.edu.ar>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems using a function in a where clause
Date: Tue, 24 Oct 2006 19:21:31 -0500
Message-ID: <20061025002131.GB26892@nasby.net> (raw)
In-Reply-To: <52cedf850610231254y11c036d4g436fa54370690689@mail.gmail.com>
References: <52cedf850610231254y11c036d4g436fa54370690689@mail.gmail.com>
On Mon, Oct 23, 2006 at 04:54:00PM -0300, Mara Dalponte wrote:
> Hello,
>
> I have a query with several join operations and applying the same
> filter condition over each involved table. This condition is a complex
> predicate over an indexed timestamp field, depending on some
> parameters.
> To factorize code, I wrote the filter into a plpgsql function, but
> the resulting query is much more slower than the first one!
A view would probably be a better idea... or create some code that
generates the code for you.
> The explain command over the original query gives the following info
> for the WHERE clause that uses the filter:
>
> ...
> Index Cond: ((_timestamp >= '2006-02-23 03:00:00'::timestamp without
> time zone) AND (_timestamp <= '2006-02-27 20:00:00.989999'::timestamp
> without time zone))
> ...
>
> The explain command for the WHERE clause using the filtering function is:
>
> ...
> Filter: include_time_date('2006-02-23'::date, '2006-02-27'::date,
> '03:00:00'::time without time zone, '20:00:00'::time without time
> zone, (_timestamp)::timestamp without time zone)
> ...
>
> It seems to not be using the index, and I think this is the reason of
> the performance gap between both solutions.
Well, it looks like include_time_date just returns a boolean, so how
could it use the index?
> How can I explicitly use this index? which type of functions shall I
> use (VOLATILE | INMUTABLE | STABLE)?
That depends on what exactly the function does. There's a pretty good
description in the CREATE FUNCTION docs.
--
Jim Nasby jim@nasby.net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
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-performance@postgresql.org
Cc: jim@nasby.net, dalponte@sol.info.unlp.edu.ar
Subject: Re: Problems using a function in a where clause
In-Reply-To: <20061025002131.GB26892@nasby.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox