X-Original-To: pgsql-bugs-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 5E29B9FA350 for ; Tue, 4 Jul 2006 11:44:23 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 08986-06 for ; Tue, 4 Jul 2006 11:44:11 -0300 (ADT) X-Greylist: delayed 06:06:29.890639 by SQLgrey- Received: from pancernik.batory.org.pl (pancernik.batory.org.pl [62.89.72.210]) by postgresql.org (Postfix) with ESMTP id 15D899FA2F3 for ; Tue, 4 Jul 2006 11:44:10 -0300 (ADT) Received: from pancernik.batory.org.pl (localhost.localdomain [127.0.0.1]) by pancernik.batory.org.pl (8.13.6/8.13.6) with ESMTP id k64Ei84F022433; Tue, 4 Jul 2006 16:44:08 +0200 Received: (from tometzky@localhost) by pancernik.batory.org.pl (8.13.6/8.13.6/Submit) id k64Ei8cR022432; Tue, 4 Jul 2006 16:44:08 +0200 Date: Tue, 4 Jul 2006 16:44:08 +0200 From: Tomasz Ostrowski To: Tom Lane Cc: pgsql-bugs@postgresql.org Subject: Re: query very slow when enable_seqscan=on Message-ID: <20060704144406.GA21511@batory.org.pl> References: <20060703203105.GA2801@batory.org.pl> <7353.1151967946@sss.pgh.pa.us> <20060704083733.GA20102@batory.org.pl> <13853.1152021389@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-2 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: <13853.1152021389@sss.pgh.pa.us> User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/19 X-Sequence-Number: 15364 On Tue, 04 Jul 2006, Tom Lane wrote: > I think the real problem here is that regex matching is the wrong > tool for the job. Have you looked into a full-text index > (tsearch2)? So much to do with so little time... I've briefly looked into it but: - it's complicated; - it is not needed - basic scan is good enough for the amount of data we have (if a sane query plan is chosen by a database); - we have data in many languages (including based on cyryllic alphabet) - languages which use different forms of the same word based on context, for example: Warszawa Warszawy Warszawie Warszawę Warszawą Warszawo All of the above could be translated to "Warsaw". So we need to support matching parts of words ("warszaw"), which I haven't seen in tsearch2 (maybe I've overlooked). We also have words, which different forms look like this: "stół" "stole" "stołu" (Polish for "table") - when we need to find it we'd need to list every possible form (about 10) or use a regex like: 'st[oó][lł]'. > With something like that, the index operator has at least got the > correct conceptual model, ie, looking for indexed words. I'm not sure > if they have any decent statistical support for it :-( but in theory > that seems doable, whereas regex estimation will always be a crapshoot. So why estimate regex expressions if there is no estimation possible? Let's set this estimate to be pessimistic (match everything or everything not null) and it will choose better plans. At least until somebody will figure out better approach. Pozdrawiam Tometzky -- Best of prhn - najzabawniejsze teksty polskiego UseNet-u http://prhn.dnsalias.org/ Chaos zawsze pokonuje porządek, gdyż jest lepiej zorganizowany. [ Terry Pratchett ]