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 007B49DC93E for ; Tue, 10 Jan 2006 19:36:46 -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 21658-06 for ; Tue, 10 Jan 2006 19:36:48 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by postgresql.org (Postfix) with ESMTP id D52139DC86A for ; Tue, 10 Jan 2006 19:36:43 -0400 (AST) Received: from [192.168.0.3] (unknown [84.12.184.6]) by smtp.nildram.co.uk (Postfix) with ESMTP id 21296269972; Tue, 10 Jan 2006 23:36:42 +0000 (GMT) Subject: Re: NOT LIKE much faster than LIKE? From: Simon Riggs To: Tom Lane Cc: Matteo Beccati , Andrea Arcangeli , pgsql-performance@postgresql.org In-Reply-To: <6398.1136931685@sss.pgh.pa.us> References: <20060110014447.GB18474@opteron.random> <24021.1136858688@sss.pgh.pa.us> <20060110022303.GA20168@opteron.random> <24372.1136861684@sss.pgh.pa.us> <43C3799D.5030103@beccati.com> <1625.1136915395@sss.pgh.pa.us> <1136930796.21025.486.camel@localhost.localdomain> <6398.1136931685@sss.pgh.pa.us> Content-Type: text/plain Date: Tue, 10 Jan 2006 23:36:45 +0000 Message-Id: <1136936205.21025.507.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 (2.2.3-2.fc4) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.057 required=5 tests=[AWL=0.057] X-Spam-Score: 0.057 X-Spam-Level: X-Archive-Number: 200601/94 X-Sequence-Number: 16572 On Tue, 2006-01-10 at 17:21 -0500, Tom Lane wrote: > Simon Riggs writes: > > I think its OK to use the MCV, but I have a problem with the current > > heuristics: they only work for randomly generated strings, since the > > selectivity goes down geometrically with length. > > We could certainly use a less aggressive curve for that. You got a > specific proposal? I read some research not too long ago that showed a frequency curve of words by syllable length. I'll dig that out tomorrow. > > I would favour the idea of dynamic sampling using a block sampling > > approach; that was a natural extension of improving ANALYZE also. > > One thing at a time please. Obtaining better statistics is one issue, > but the one at hand here is what to do given particular statistics. I meant use the same sampling approach as I was proposing for ANALYZE, but do this at plan time for the query. That way we can apply the function directly to the sampled rows and estimate selectivity. I specifically didn't mention that in the Ndistinct discussion because I didn't want to confuse the subject further, but the underlying block sampling method would be identical, so the code is already almost there...we just need to eval the RestrictInfo against the sampled tuples. Best Regards, Simon Riggs