Message-ID: <475D3947.7000107@archonet.com>
Date: Mon, 10 Dec 2007 13:04:07 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.9 (X11/20071031)
MIME-Version: 1.0
To: =?ISO-8859-2?Q?Piotr_Gasid=B3o?= <quaker@barbara.eu.org>
CC: pgsql-performance@postgresql.org
Subject: Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.
References: <475D37D9.3000208@barbara.eu.org>
In-Reply-To: <475D37D9.3000208@barbara.eu.org>
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: quoted-printable

Piotr Gasid=B3o wrote:
> Some performance loss, but OK. Now I've changed "=3D" into "LIKE" to us=
e=20
> users_user_name_unique_text_pattern_ops index and rerun query:
>=20
> explain analyze select user_login('quaker');
>=20
>                                       QUERY PLAN
> -----------------------------------------------------------------------=
---------------=20
>=20
>  Result  (cost=3D0.00..0.01 rows=3D1 width=3D0) (actual time=3D41.606..=
41.608=20
> rows=3D1 loops=3D1)
>  Total runtime: 41.629 ms
> (2 rows)
>=20
> Second run give 61.061 ms. So no improvements.
>=20
> Why PL/PGSQL is unable to proper utilize=20
> users_user_name_unique_text_pattern_ops?

It plans the query just once for the pl/pgsql function. That means it=20
doesn't know whether you are passing in a name '%foo' which can't use=20
the index. Since only one plan can be used it has to use a scan of the=20
table.

You can use EXECUTE to get plpgsql to plan the query each time it is=20
called. That should let it recognise that it can use the index (if it=20
can, of course).

--=20
   Richard Huxton
   Archonet Ltd