Hello everybody,
I am trying to speed up a query on an integer column by defining an
index as follows
> CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text)
using varchar_ops);
on column "main_subject".
I had hoped to get speedups for right-fuzzy LIKE-searches, but EXPLAIN
ANALYZE yields that the index is not used:
> EXPLAIN ANALYZE SELECT COUNT(*) FROM pdb.main WHERE
lower(main_subject::text) LIKE lower('10%'::text);
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------
Aggregate (cost=137759.92..137759.93 rows=1 width=0) (actual
time=3421.696..3421.697 rows=1 loops=1)
-> Seq Scan on main (cost=0.00..137727.17 rows=13096 width=0)
(actual time=0.036..3300.961 rows=77577 loops=1)
Filter: (lower((main_subject)::text) ~~ '10%'::text)
Total runtime: 3421.751 ms
(4 Zeilen)
Am I misunderstanding the concept of functional indexes? Is there
another way to achieve
Any help is greatly
appreciated.
Yours,
Alexander Presber
begin:vcard
fn:Alexander Presber
n:Presber;Alexander
org;quoted-printable:Wei=C3=9Fhuhn & Wei=C3=9Fhuhn Kommunikationsmanagement GmbH;Softwareentwicklung
adr;quoted-printable;dom:;;Warschauer Stra=C3=9Fe 58a;Berlin;;10243
email;internet:aljoscha@xxxxxxxxxxxx
title:Dipl.-Phys.
tel;work:61654 - 214
x-mozilla-html:FALSE
version:2.1
end:vcard
