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@sigaev.ru
                                                    WWW: http://www.sigaev.ru/