Re: Slow functional indexes?

Stuart Bishop 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.
>
> 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).
>
> (The following log is also at
> http://rafb.net/paste/results/vKVuyi47.nln.html if that is more readable)

Here is a minimal test case that demonstrates the issue. Can anyone else
reproduce these results? Of the four EXPLAIN ANALYZE SELECT statements at
the end, the one that orders by a user created IMMUTABLE stored procedure is
consistently slower than the other three variants.

BEGIN;
DROP TABLE TestCase;
COMMIT;
ABORT;

BEGIN;
CREATE TABLE TestCase (name text, alt_name text);

CREATE OR REPLACE FUNCTION munge(s text) RETURNS text
IMMUTABLE RETURNS NULL ON NULL INPUT
LANGUAGE plpgsql AS $$
BEGIN
RETURN lower(s);
END;
$$;

-- Fill the table with random strings
CREATE OR REPLACE FUNCTION fill_testcase(num_rows int) RETURNS boolean
LANGUAGE plpgsql AS
$$
DECLARE
row_num int;
char_num int;
name text;
BEGIN
FOR row_num IN 1..num_rows LOOP
name := '';
FOR char_num IN 1..round(random() * 100) LOOP
name := name || chr((
round(random() * (ascii('z') - ascii('!'))) + ascii('!')
)::int);
END LOOP;
INSERT INTO TestCase VALUES (name, lower(name));
IF row_num % 20000 = 0 THEN
RAISE NOTICE '% of % rows inserted', row_num, num_rows;
END IF;
END LOOP;
RETURN TRUE;
END;
$$;

SELECT fill_testcase(500000);

CREATE INDEX testcase__name__idx ON TestCase(name);
CREATE INDEX testcase__lower__idx ON TestCase(lower(name));
CREATE INDEX testcase__munge__idx ON TestCase(munge(name));
CREATE INDEX testcase__alt_name__idx ON TestCase(alt_name);

COMMIT;

ANALYZE TestCase;

EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY name;
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY lower(name);
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY munge(name);
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY alt_name;

EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY name;
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY lower(name);
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY munge(name);
EXPLAIN ANALYZE SELECT * FROM TestCase ORDER BY alt_name;

--
Stuart Bishop <stuart(at)stuartbishop(dot)net>
http://www.stuartbishop.net/