Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id BCB59B5DBD6 for ; Wed, 10 Aug 2011 14:15:42 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 36731-07 for ; Wed, 10 Aug 2011 17:15:36 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.gransy.com (mail.gransy.com [82.208.29.194]) by mail.postgresql.org (Postfix) with ESMTP id B6B8FB5DBCA for ; Wed, 10 Aug 2011 14:15:36 -0300 (ADT) Received: from antispam.gransy.com (antispam.gransy.com [77.78.104.124]) by mail.gransy.com (Postfix) with ESMTP id ADE7C4A2175 for ; Wed, 10 Aug 2011 19:15:34 +0200 (CEST) Received: from mail.gransy.com ([82.208.29.194]) by antispam.gransy.com (antispam.gransy.com [77.78.104.124]) (amavisd-new, port 10024) with ESMTP id MGWGE6s0B3vl for ; Wed, 10 Aug 2011 19:15:34 +0200 (CEST) Received: from [192.168.1.227] (ip-78-45-133-255.net.upcbroadband.cz [78.45.133.255]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by mail.gransy.com (Postfix) with ESMTPSA id 92C424A2042 for ; Wed, 10 Aug 2011 19:15:34 +0200 (CEST) Message-ID: <4E42BCC0.3010007@fuzzy.cz> Date: Wed, 10 Aug 2011 19:15:44 +0200 From: Tomas Vondra User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:5.0) Gecko/20110624 Thunderbird/5.0 MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Re: poor pefrormance with regexp searches on large tables References: <4E425D2D020000250003FD32@gw.wicourts.gov> In-Reply-To: X-Enigmail-Version: 1.2 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201108/123 X-Sequence-Number: 44570 Dne 10.8.2011 19:01, Grzegorz Blinowski napsal(a): > However, changing shared_mem didn't help. We also checked system I/O > stats during the query - and in fact there is almost no IO (even with > suboptimal shared_memory). So the problem is not disk transfer/access > but rather the way Postgres handles regexp queries... As I have wirtten > it is difficult to rewrite the query syntax (the SQL generation in this > app is quite complex), but it should be relatively easy to at least join > all OR clauses into one regexp, I can try this from the psql CLI. I will > post an update if anything interesting happens... Can you post EXPLAIN ANALYZE, prefferably using explain.depesz.com? Tomas