Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D1C1D9FB21D for ; Wed, 25 Oct 2006 12:32:44 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 81461-07 for ; Wed, 25 Oct 2006 12:32:40 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 8C6A49F98F8 for ; Wed, 25 Oct 2006 12:32:40 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id EEC7156446; Wed, 25 Oct 2006 10:32:39 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Wed, 25 Oct 2006 10:32:37 -0500 Date: Wed, 25 Oct 2006 10:32:37 -0500 From: "Jim C. Nasby" To: Mara Dalponte Cc: pgsql-performance@postgresql.org Subject: Re: Problems using a function in a where clause Message-ID: <20061025153236.GR26892@nasby.net> References: <52cedf850610231254y11c036d4g436fa54370690689@mail.gmail.com> <20061025002131.GB26892@nasby.net> <52cedf850610250355l425d50deieb6750b6b0f4b4d9@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <52cedf850610250355l425d50deieb6750b6b0f4b4d9@mail.gmail.com> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:061025:dalponte@sol.info.unlp.edu.ar::jcmViaMGLvndKlvc:00000000 000000000000000000000000BFwc X-Hashcash: 1:20:061025:pgsql-performance@postgresql.org::UnxCnXIdOg9kMGmE:00000 0000000000000000000000003H0Y X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.076 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/517 X-Sequence-Number: 21646 On Wed, Oct 25, 2006 at 07:55:38AM -0300, Mara Dalponte wrote: > On 10/24/06, Jim C. Nasby wrote: > >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. > > Thank, but the filter function needs some external parameters, so a > view wont be appropiate. Anyway, your second possibility could work! > > >> 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? > > I mean that in the old query the index is used (because is a > comparative condition over an indexed timestamp field), but not in the > new one, where the function is used. Is there some kind of "inline" > function type? No, unfortunately. Your best bet is to add the most important filter criteria by hand, or write code that writes the code (which is what I'd probably do). -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)