Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 236F5634E05 for ; Mon, 5 Apr 2010 11:33:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 92439-06-2 for ; Mon, 5 Apr 2010 14:33:11 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from depesz.com (dave.depesz.com [78.46.78.198]) by mail.postgresql.org (Postfix) with ESMTP id A8434632D10 for ; Mon, 5 Apr 2010 11:32:46 -0300 (ADT) Received: from martin.depesz.com ([188.40.39.14] helo=depesz.com) by depesz.com with esmtpa (Exim 4.69) (envelope-from ) id 1NynM4-00014A-Ds; Mon, 05 Apr 2010 16:32:44 +0200 Date: Mon, 5 Apr 2010 16:32:44 +0200 From: hubert depesz lubaczewski To: Oliver Kindernay Cc: pgsql-performance@postgresql.org Subject: Re: Huge table searching optimization Message-ID: <20100405143244.GB31050@depesz.com> Reply-To: depesz@depesz.com References: MIME-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.18 (2008-05-17) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.439 tagged_above=-10 required=5 tests=AWL=0.160, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201004/36 X-Sequence-Number: 38286 On Mon, Apr 05, 2010 at 04:28:35PM +0200, Oliver Kindernay wrote: > Hi, I have table with just on column named url (it's not real url, > just random string for testing purposes), type text. I have lots of > entries in it (it's dynamic, i add and remove them on the fly), 100 > 000 and more. I've created index on this table to optimize > "searching". I just want to test if some "url" is in in the table, so > i am using this request: > > select url from test2 where url ~* '^URLVALUE\\s*$'; > > there's \\s* because of padding. Here is the analyze: > > postgres=# explain analyze select url from test2 where url ~* '^zyxel\\s*$'; > WARNING: nonstandard use of \\ in a string literal > LINE 1: ...plain analyze select url from test2 where url ~* '^zyxel\\s... > ^ > HINT: Use the escape string syntax for backslashes, e.g., E'\\'. > QUERY PLAN > ------------------------------------------------------------------------------------------------------- > Seq Scan on test2 (cost=0.00..1726.00 rows=10 width=9) (actual > time=156.489..156.502 rows=1 loops=1) > Filter: (url ~* '^zyxel\\s*$'::text) > Total runtime: 156.538 ms > (3 rows) > It takes 156 ms, it's too much for my purposes, so i want to decrease > it. So what can I use for optimizing this request? Again, I just want > to test if "url" ("zyxel" in this examlpe) is in the table. add trigger to remove spaces from end of string on insert and update, and then use normal = operator. Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007