Re: Functional Index

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(at)sigaev(dot)ru
WWW: http://www.sigaev.ru/