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.
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 |