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.
