Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 1CE7764FF01 for ; Wed, 26 Nov 2008 15:07:42 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 29548-10 for ; Wed, 26 Nov 2008 15:07:40 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ptb-relay03.plus.net (ptb-relay03.plus.net [212.159.14.147]) by mail.postgresql.org (Postfix) with ESMTP id 1F49A64FD32 for ; Wed, 26 Nov 2008 15:07:39 -0400 (AST) Received: from [84.51.143.99] (helo=server3.office.archonet.com) by ptb-relay03.plus.net with esmtp (Exim) id 1L5Pjd-0005uu-Cn; Wed, 26 Nov 2008 19:07:37 +0000 Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36]) by server3.office.archonet.com (Postfix) with ESMTP id 24E33274066; Wed, 26 Nov 2008 19:07:37 +0000 (GMT) Message-ID: <492D9E78.9080902@archonet.com> Date: Wed, 26 Nov 2008 19:07:36 +0000 From: Richard Huxton User-Agent: Thunderbird 2.0.0.18 (X11/20081105) MIME-Version: 1.0 To: Andrus CC: mweilguni@sime.com, pgsql-performance@postgresql.org Subject: Re: Increasing pattern index query speed References: <492A7591.3000700@archonet.com> <492D27C8.7090206@archonet.com> <4936D68EF8C84087BBB9212C4019D5D8@andrusnotebook> In-Reply-To: <4936D68EF8C84087BBB9212C4019D5D8@andrusnotebook> Content-Type: text/plain; charset=windows-1257 Content-Transfer-Encoding: 7bit X-Plusnet-Relay: 768d8b1bd7f3e8f5cf1f2efab1cea816 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200811/263 X-Sequence-Number: 31994 Andrus wrote: > Richard and Mario, > >> You can't use xxx_pattern_ops indexes for non-pattern tests. > > I missed regular index. Sorry for that. Now issue with testcase is > solved. Thank you very much. > > I researched issue in live 8.1.4 db a bit more. > Performed vacuum and whole db reindex. > Tried several times to run two same pattern queries in quiet db. And the results were? > additonal condition One problem at a time. Let's get the pattern-matching speed problems on your live server sorted, then we can look at different queries. -- Richard Huxton Archonet Ltd