Message-ID: <45387666.204@canonical.com>
Date: Fri, 20 Oct 2006 14:10:30 +0700
From: Stuart Bishop <stuart.bishop@canonical.com>
Organization: Canonical Ltd.
User-Agent: Thunderbird 1.5.0.7 (X11/20060922)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Slow functional indexes?
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="------------enig8169DC00D0D3F7A9C46701EA"

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

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 int=
4
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 identic=
al
query plans, however runtime differences are significantly slower using t=
he
functional index. If I add a new column to the table containing the resul=
t
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)=


demo=3D# vacuum full analyze person;
VACUUM
demo=3D# reindex table person;
REINDEX
demo=3D# cluster person_pkey on person;
CLUSTER
demo=3D# explain analyze select * from person order by id offset 527000 l=
imit 50;
                                                                 QUERY PL=
AN
-------------------------------------------------------------------------=
--------------------------------------------------------------------
 Limit  (cost=3D37039.09..37042.61 rows=3D50 width=3D531) (actual
time=3D1870.393..1870.643 rows=3D50 loops=3D1)
   ->  Index Scan using person_pkey on person  (cost=3D0.00..37093.42
rows=3D527773 width=3D531) (actual time=3D0.077..1133.659 rows=3D527050 l=
oops=3D1)
 Total runtime: 1870.792 ms
(3 rows)

demo=3D# cluster person_name_key on person;
CLUSTER
demo=3D# explain analyze select * from person order by name offset 527000=

limit 50;
                                                                   QUERY =
PLAN
-------------------------------------------------------------------------=
------------------------------------------------------------------------
 Limit  (cost=3D63727.87..63733.91 rows=3D50 width=3D531) (actual
time=3D1865.769..1866.028 rows=3D50 loops=3D1)
   ->  Index Scan using person_name_key on person  (cost=3D0.00..63821.34=

rows=3D527773 width=3D531) (actual time=3D0.068..1138.649 rows=3D527050 l=
oops=3D1)
 Total runtime: 1866.153 ms
(3 rows)

demo=3D# cluster person_sorting_idx on person;
CLUSTER
demo=3D# explain analyze select * from person order by
person_sort_key(displayname,name) offset 527000 limit 50;
                                                                     QUER=
Y PLAN
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
---
 Limit  (cost=3D65806.62..65812.86 rows=3D50 width=3D531) (actual
time=3D13846.677..13848.102 rows=3D50 loops=3D1)
   ->  Index Scan using person_sorting_idx on person  (cost=3D0.00..65903=
=2E14
rows=3D527773 width=3D531) (actual time=3D0.214..13093.090 rows=3D527050 =
loops=3D1)
 Total runtime: 13848.254 ms
(3 rows)

demo=3D# alter table person add column sort_key text;
ALTER TABLE
demo=3D# update person set sort_key=3Dperson_sort_key(displayname,name);
UPDATE 527773
demo=3D# create index person_sort_key_idx on person(sort_key);
CREATE INDEX
demo=3D# vacuum analyze person;
VACUUM
demo=3D# cluster person_sort_key_idx on person;
CLUSTER
demo=3D# explain analyze select * from person order by sort_key offset 52=
7000
limit 50;
                                                                     QUER=
Y PLAN
-------------------------------------------------------------------------=
-------------------------------------------------------------------------=
---
 Limit  (cost=3D41069.28..41073.18 rows=3D50 width=3D553) (actual
time=3D1999.456..1999.724 rows=3D50 loops=3D1)
   ->  Index Scan using person_sort_key_idx on person  (cost=3D0.00..4112=
9.52
rows=3D527773 width=3D553) (actual time=3D0.079..1274.952 rows=3D527050 l=
oops=3D1)
 Total runtime: 1999.858 ms
(3 rows)


--=20
Stuart Bishop <stuart.bishop@canonical.com>   http://www.canonical.com/
Canonical Ltd.                                http://www.ubuntu.com/


--------------enig8169DC00D0D3F7A9C46701EA
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)

iD8DBQFFOHZnAfqZj7rGN0oRAkDaAJ48PODZmdJ3ncdxC9tMl+x460jv1QCeOybR
0FPTRk77EsffjE47eayzkhA=
=pmrv
-----END PGP SIGNATURE-----

--------------enig8169DC00D0D3F7A9C46701EA--