X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id E4FAD9DC95F for ; Mon, 9 Jan 2006 22:04:48 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 69064-04 for ; Mon, 9 Jan 2006 22:04:52 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id A76E39DC82B for ; Mon, 9 Jan 2006 22:04:46 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id k0A24mht024022; Mon, 9 Jan 2006 21:04:48 -0500 (EST) To: Andrea Arcangeli cc: pgsql-performance@postgresql.org Subject: Re: NOT LIKE much faster than LIKE? In-reply-to: <20060110014447.GB18474@opteron.random> References: <20060110014447.GB18474@opteron.random> Comments: In-reply-to Andrea Arcangeli message dated "Tue, 10 Jan 2006 02:44:47 +0100" Date: Mon, 09 Jan 2006 21:04:48 -0500 Message-ID: <24021.1136858688@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.077 required=5 tests=[AWL=0.077] X-Spam-Score: 0.077 X-Spam-Level: X-Archive-Number: 200601/73 X-Sequence-Number: 16551 Andrea Arcangeli writes: > It just makes no sense to me that the planner takes a difference > decision based on a "not". Why in the world would you think that? In general a NOT will change the selectivity of the WHERE condition tremendously. If the planner weren't sensitive to that, *that* would be a bug. The only case where it's irrelevant is if the selectivity of the base condition is exactly 50%, which is not a very reasonable default guess for LIKE. It sounds to me that the problem is misestimation of the selectivity of the LIKE pattern --- the planner is going to think that LIKE '%% PREEMPT %%' is fairly selective because of the rather long match text, when in reality it's probably not so selective on your data. But we don't keep any statistics that would allow the actual number of matching rows to be estimated well. You might want to think about changing your data representation so that the pattern-match can be replaced by a boolean column, or some such, so that the existing statistics code can make a more reasonable estimate how many rows are selected. regards, tom lane