Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 0534E9FB2BB for ; Tue, 24 Oct 2006 21:21:39 -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 81164-06 for ; Tue, 24 Oct 2006 21:21:35 -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 F2CFF9FA65C for ; Tue, 24 Oct 2006 21:21:34 -0300 (ADT) Received: by noel.decibel.org (Postfix, from userid 1001) id 5A7B956427; Tue, 24 Oct 2006 19:21:33 -0500 (CDT) Received: (hashcash-sendmail, from uid 1001); Tue, 24 Oct 2006 19:21:31 -0500 Date: Tue, 24 Oct 2006 19:21:31 -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: <20061025002131.GB26892@nasby.net> References: <52cedf850610231254y11c036d4g436fa54370690689@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <52cedf850610231254y11c036d4g436fa54370690689@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::cUjKmNJmxV6GszT9:00000000 0000000000000000000000001LuK X-Hashcash: 1:20:061025:pgsql-performance@postgresql.org::WEcNO6KjFDNggzZ8:00000 0000000000000000000000001V8U X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.075 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200610/506 X-Sequence-Number: 21635 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)