Message-ID: <454E4AC7.9020201@stuartbishop.net>
Date: Sun, 05 Nov 2006 12:34:15 -0800
From: Stuart Bishop <stuart@stuartbishop.net>
User-Agent: Thunderbird 1.5.0.7 (X11/20060922)
MIME-Version: 1.0
To: Stuart Bishop <stuart.bishop@canonical.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Slow functional indexes?
References: <45387666.204@canonical.com>
In-Reply-To: <45387666.204@canonical.com>
OpenPGP: id=BAC6374A;
	url=http://stuartbishop.net/stuartbishop.asc
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enigC77A716D8670AEAC7C4728B1"

This is an OpenPGP/MIME signed message (RFC 2440 and 3156)
--------------enigC77A716D8670AEAC7C4728B1
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

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 i=
nt4
> primary key, an indexed unique text 'name' column and a functional inde=
x of
> type text. The function (person_sort_key()) is declared IMMUTABLE and
> RETURNS NULL ON NULL INPUT.
>=20
> A simple query ordering by each of these columns generates nearly ident=
ical
> query plans, however runtime differences are significantly slower using=
 the
> functional index. If I add a new column to the table containing the res=
ult
> of the function, index it and query ordering by this new column then th=
e
> runtime is nearly an order of magnitude faster than using the functiona=
l
> 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 readabl=
e)

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 :=3D '';
        FOR  char_num IN 1..round(random() * 100) LOOP
            name :=3D name || chr((
                round(random() * (ascii('z') - ascii('!'))) + ascii('!')
                )::int);
        END LOOP;
        INSERT INTO TestCase VALUES (name, lower(name));
        IF row_num % 20000 =3D 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;


--=20
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/


--------------enigC77A716D8670AEAC7C4728B1
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2.2 (GNU/Linux)

iD8DBQFFTkrHAfqZj7rGN0oRAh7rAJ4ooO2tP8rFP4OV/Jj8oN+jZp40sQCeKu1W
wh3EC3bIQOBYh2Z1SSxeAm8=
=PlFc
-----END PGP SIGNATURE-----

--------------enigC77A716D8670AEAC7C4728B1--