Huge table searching optimization

From: Oliver Kindernay <oliver(dot)kindernay(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Huge table searching optimization
Date: 2010-04-05 14:28:35
Message-ID: w2y53553b7f1004050728j90050ad3wc9fa4f6c958c3263@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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.

Some info:

version(): PostgreSQL 8.4.2 on i486-slackware-linux-gnu, compiled by
GCC gcc (GCC) 4.3.3, 32-bit
Ram: 500 MB
CPU: 2.6 Ghz (it's kvm virtualized, i don't know exact type, it's one core cpu)

Thank you.

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message hubert depesz lubaczewski 2010-04-05 14:32:44 Re: Huge table searching optimization
Previous Message Bruce Momjian 2010-04-05 12:09:22 Re: temp table "on commit delete rows": transaction overhead