From: hubert depesz lubaczewski <depesz@depesz.com>
To: Oliver Kindernay <oliver.kindernay@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Huge table searching optimization
Date: Mon, 5 Apr 2010 16:32:44 +0200
Message-ID: <20100405143244.GB31050@depesz.com> (raw)
In-Reply-To: <w2y53553b7f1004050728j90050ad3wc9fa4f6c958c3263@mail.gmail.com>
References: <w2y53553b7f1004050728j90050ad3wc9fa4f6c958c3263@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: depesz@depesz.com, oliver.kindernay@gmail.com
Subject: Re: Huge table searching optimization
In-Reply-To: <20100405143244.GB31050@depesz.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox