Re: Slow functional indexes?

On 10/20/06, Stuart Bishop <stuart.bishop@canonical.com> wrote:
> I would like to understand what causes some of my indexes to be slower to
> use than others with PostgreSQL 8.1. On a particular table, I have an int4
> primary key, an indexed unique text 'name' column and a functional index of
> type text. The function (person_sort_key()) is declared IMMUTABLE and
> RETURNS NULL ON NULL INPUT.

database will not allow you to create index if the function is not immutable.

> A simple query ordering by each of these columns generates nearly identical
> query plans, however runtime differences are significantly slower using the
> functional index. If I add a new column to the table containing the result
> of the function, index it and query ordering by this new column then the
> runtime is nearly an order of magnitude faster than using the functional
> index (and again, query plans are nearly identical).

> demo=# explain analyze select * from person order by id offset 527000 limit 50;
>                                                                  QUERY PLAN

it looks you just turned up a bad interaction between a functional
index and 'offset' probably your function is getting executed extra
times or there is a sort going on.  however, I'd suggest not using
'offset', because its bad design.

merlin