From: Stuart Bishop <stuart@stuartbishop.net>
To: Stuart Bishop <stuart.bishop@canonical.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow functional indexes?
Date: Sun, 05 Nov 2006 12:34:15 -0800
Message-ID: <454E4AC7.9020201@stuartbishop.net> (raw)
In-Reply-To: <45387666.204@canonical.com>
References: <45387666.204@canonical.com>
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@stuartbishop.net>
http://www.stuartbishop.net/Attachments:
signature.asc (application/pgp-signature, 190B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stuart@stuartbishop.net, stuart.bishop@canonical.com
Subject: Re: Slow functional indexes?
In-Reply-To: <454E4AC7.9020201@stuartbishop.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox