On 10/20/06, Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx> 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
