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 D6D6B9DC83C for ; Tue, 10 Jan 2006 05:08:31 -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 45643-10 for ; Tue, 10 Jan 2006 05:08:32 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from roast.hq.mobyt.it (194-185-112-82.f5.ngi.it [194.185.112.82]) by postgresql.org (Postfix) with SMTP id F3A499DC809 for ; Tue, 10 Jan 2006 05:08:26 -0400 (AST) Received: (qmail 26923 invoked from network); 10 Jan 2006 10:05:19 +0100 Received: from webdev.hq.mobyt.it (HELO ?10.20.20.4?) (10.20.20.4) by 0 with SMTP; 10 Jan 2006 10:05:19 +0100 Message-ID: <43C3799D.5030103@beccati.com> Date: Tue, 10 Jan 2006 10:08:45 +0100 From: Matteo Beccati User-Agent: Thunderbird 1.5 (Windows/20051201) MIME-Version: 1.0 To: Tom Lane CC: Andrea Arcangeli , pgsql-performance@postgresql.org Subject: Re: NOT LIKE much faster than LIKE? References: <20060110014447.GB18474@opteron.random> <24021.1136858688@sss.pgh.pa.us> <20060110022303.GA20168@opteron.random> <24372.1136861684@sss.pgh.pa.us> In-Reply-To: <24372.1136861684@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/83 X-Sequence-Number: 16561 Hi, > I did just think of something we could improve though. The pattern > selectivity code doesn't make any use of the statistics about "most > common values". For a constant pattern, we could actually apply the > pattern test with each common value and derive answers that are exact > for the portion of the population represented by the most-common-values > list. If the MCV list covers a large fraction of the population then > this would be a big leg up in accuracy. Dunno if that applies to your > particular case or not, but it seems worth doing ... This reminds me what I did in a patch which is currently on hold for the next release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold http://candle.pha.pa.us/mhonarc/patches_hold/msg00026.html The patch was addressing a similar issue when using ltree <@ and @> operator on an unbalanced tree. Best regards -- Matteo Beccati http://phpadsnew.com http://phppgads.com