From: Andres Freund <andres@anarazel.de>
To: pgsql-performance@postgresql.org
Subject: Re: Huge table searching optimization
Date: Mon, 5 Apr 2010 18:10:57 +0200
User-Agent: KMail/1.13.2 (Linux/2.6.34-rc3-andres-00163-g5e11611; KDE/4.4.2;
	x86_64; ; )
Cc: Oliver Kindernay <oliver.kindernay@gmail.com>
References: <w2y53553b7f1004050728j90050ad3wc9fa4f6c958c3263@mail.gmail.com>
In-Reply-To: <w2y53553b7f1004050728j90050ad3wc9fa4f6c958c3263@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Message-Id: <201004051810.57673.andres@anarazel.de>

Hi,

On Monday 05 April 2010 16:28:35 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.
> 
Depending on your locale it might be sensible to create a text_pattern_ops 
index - see the following link: 
http://www.postgresql.org/docs/current/static/indexes-opclass.html

Like suggested by depesz it would be far better to remove the padding and do 
exact lookups though.

Andres