Thread: Functional Index
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
Attachment
use varchar_pattern_ops operator class, LIKE cannot use varchar_ops for non-C
locales.
Alexander Presber wrote:
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/
On Wed, 22 Nov 2006, Alexander Presber wrote: > 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, IIRC, unless you're in C locale, you'll want varchar_pattern_ops rather than varchar_ops on the index to make it considered for a LIKE search.
Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Wed, 22 Nov 2006, Alexander Presber wrote:
>> CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text)
>> using varchar_ops);
> IIRC, unless you're in C locale, you'll want varchar_pattern_ops rather
> than varchar_ops on the index to make it considered for a LIKE search.
text_pattern_ops would be better, seeing that the output of lower() is
text not varchar. I'm a bit surprised the planner is able to make use
of this index at all.
regards, tom lane
On Wed, Nov 22, 2006 at 11:24:33AM -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Stephan Szabo <sszabo@megazone.bigpanda.com> writes: > > On Wed, 22 Nov 2006, Alexander Presber wrote: > >> CREATE INDEX idx_main_subject ON pdb.main (lower(main_subject::text) > >> using varchar_ops); > > > IIRC, unless you're in C locale, you'll want varchar_pattern_ops rather > > than varchar_ops on the index to make it considered for a LIKE search. > > text_pattern_ops would be better, seeing that the output of lower() is > text not varchar. I'm a bit surprised the planner is able to make use > of this index at all. Since the original poster Alex is a colleage of mine and just ran out the door, let me pass on his big THANK YOU on his behalf. He is all smiles now, and the query is fast now. He should also be wearing that ole' brown paper bag, since we even have an inhouse wiki page documenting the need for varchar_pattern_ops :-) Anyway, thanks a bunch everybody! regards, Alex & bkw