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 C98919FA350 for ; Tue, 4 Jul 2006 10:56:38 -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 03112-04 for ; Tue, 4 Jul 2006 10:56:33 -0300 (ADT) 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 4B61E9FA2F3 for ; Tue, 4 Jul 2006 10:56:33 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k64DuTCi013854; Tue, 4 Jul 2006 09:56:29 -0400 (EDT) To: Tomasz Ostrowski cc: pgsql-bugs@postgresql.org Subject: Re: query very slow when enable_seqscan=on In-reply-to: <20060704083733.GA20102@batory.org.pl> References: <20060703203105.GA2801@batory.org.pl> <7353.1151967946@sss.pgh.pa.us> <20060704083733.GA20102@batory.org.pl> Comments: In-reply-to Tomasz Ostrowski message dated "Tue, 04 Jul 2006 10:37:33 +0200" Date: Tue, 04 Jul 2006 09:56:29 -0400 Message-ID: <13853.1152021389@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200607/17 X-Sequence-Number: 15362 Tomasz Ostrowski writes: > I think because there is no good solution to this - no statistical > information is going to predict how much data will match a regular > expression. Well, it's certainly hard to imagine simple stats that would let the code guess that, say, "warsa" and "warsaw" match nearly the same (large) number of rows while "warsawq" matches nothing. 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)? 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. regards, tom lane