From: Richard Huxton <dev@archonet.com>
To: Piotr Gasidło <quaker@barbara.eu.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.
Date: Mon, 10 Dec 2007 13:04:07 +0000
Message-ID: <475D3947.7000107@archonet.com> (raw)
In-Reply-To: <475D37D9.3000208@barbara.eu.org>
References: <475D37D9.3000208@barbara.eu.org>
Piotr Gasidło wrote:
> Some performance loss, but OK. Now I've changed "=" into "LIKE" to use
> users_user_name_unique_text_pattern_ops index and rerun query:
>
> explain analyze select user_login('quaker');
>
> QUERY PLAN
> --------------------------------------------------------------------------------------
>
> Result (cost=0.00..0.01 rows=1 width=0) (actual time=41.606..41.608
> rows=1 loops=1)
> Total runtime: 41.629 ms
> (2 rows)
>
> Second run give 61.061 ms. So no improvements.
>
> Why PL/PGSQL is unable to proper utilize
> users_user_name_unique_text_pattern_ops?
It plans the query just once for the pl/pgsql function. That means it
doesn't know whether you are passing in a name '%foo' which can't use
the index. Since only one plan can be used it has to use a scan of the
table.
You can use EXECUTE to get plpgsql to plan the query each time it is
called. That should let it recognise that it can use the index (if it
can, of course).
--
Richard Huxton
Archonet Ltd
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: dev@archonet.com, quaker@barbara.eu.org
Subject: Re: Index on VARCHAR with text_pattern_ops inside PL/PGSQL procedure.
In-Reply-To: <475D3947.7000107@archonet.com>
* 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