X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id 3B8DA30EC11 for ; Mon, 16 Jun 2003 05:43:47 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 52188-04 for ; Mon, 16 Jun 2003 02:43:36 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 7247430E8CA for ; Mon, 16 Jun 2003 02:43:36 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h5G5hYU6026794; Mon, 16 Jun 2003 01:43:34 -0400 (EDT) To: jim@nasby.net Cc: pgsql-performance@postgresql.org Subject: Re: Partial index where clause not filtering through In-reply-to: <20030616003118.S66185@flake.decibel.org> References: <20030616003118.S66185@flake.decibel.org> Comments: In-reply-to "Jim C. Nasby" message dated "Mon, 16 Jun 2003 00:31:18 -0500" Date: Mon, 16 Jun 2003 01:43:34 -0400 Message-ID: <26793.1055742214@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200306/78 X-Sequence-Number: 2162 "Jim C. Nasby" writes: > As-is, it won't use the index. i.address_v IS NOT NULL AND s.addr_high_v > >= i.address_v should mandate that s.addr_high_v must be not-null, Actually, if the >= operator is strict then it implies both NOT NULL conditions. But I am not excited about putting some kind of theorem prover into the partial-index logic. That is a recipe for chewing up huge numbers of cycles trying (and, likely, failing) to prove that a partial index is safe to use with the current query. Inference rules that are limited to strict operators and NOT NULL clauses wouldn't cost as much as a general theorem prover, but they'd not find useful improvements as often, either. So the question is still whether the game is worth the candle. How often do you think this would win, and is that worth the planner cycles expended on every query to find out if it wins? regards, tom lane