X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D95EF32A154 for ; Fri, 1 Oct 2004 23:07:03 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 76137-02 for ; Fri, 1 Oct 2004 22:06:53 +0000 (GMT) Received: from shire.ontko.com (shire.ontko.com [199.164.165.1]) by svr1.postgresql.org (Postfix) with ESMTP id 72D0F329D5F for ; Fri, 1 Oct 2004 23:06:54 +0100 (BST) Received: from cormallen.doxpop.com (ryanv@cormallen.doxpop.com [199.164.165.137]) by shire.ontko.com (8.12.3/8.12.3/Debian-7.1) with ESMTP id i91M6skX009733 for ; Fri, 1 Oct 2004 17:06:54 -0500 From: Ryan VanMiddlesworth To: pgsql-performance@postgresql.org Subject: Query planner problem Date: Fri, 1 Oct 2004 17:06:54 -0500 User-Agent: KMail/1.7 MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200410011706.54231.ryan@vanmiddlesworth.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/17 X-Sequence-Number: 8493 Okay, I've got two queries that I think the planner should reduce to be logically equivalent but it's not happening. The example queries below have been simplified as much as possible while still producing the problem. What I'm trying to do is create a single prepared statement that can handle null parameters rather than have to dynamically generate the statement in my app code based on supplied parameters. Basically the date constants below would be substituted with parameters supplied on a web search form (or nulls). Here is the query and EXPLAIN that runs quickly: SELECT case_id FROM case_data WHERE case_filed_date > '2004-09-16' AND case_filed_date < '2004-09-20' QUERY PLAN ------------------------------------------------------------- Index Scan using case_data_case_filed_date on case_data (cost=0.00..13790.52 rows=3614 width=18) Index Cond: ((case_filed_date > '2004-09-16'::date) AND (case_filed_date < '2004-09-20'::date)) And here is the query and EXPLAIN from the version that I believe the planner should reduce to be logically equivalent: SELECT case_id FROM case_data WHERE (('2004-09-16' IS NULL) OR (case_filed_date > '2004-09-16')) AND (('2004-09-20' IS NULL) OR (case_filed_date < '2004-09-20')) QUERY PLAN ------------------------------------------------------------- Seq Scan on case_data (cost=0.00..107422.02 rows=27509 width=18) Filter: ((('2004-09-16' IS NULL) OR (case_filed_date > '2004-09-16'::date)) AND (('2004-09-20' IS NULL) OR (case_filed_date < '2004-09-20'::date))) I was hoping that the null comparisons would get folded out by the planner relatively cheaply. But as you can see, the first query uses indexes and the second one uses sequence scans, thereby taking much longer. I guess my question is - is there a better way to accomplish what I'm doing in SQL or am I going to have to dynamically generate the statement based on supplied parameters? Thanks, Ryan