From: "Dave Dutcher" <dave@tridecap.com>
To: "'Vincenzo Romano'" <vincenzo.romano@gmail.com>,
	<pgsql-performance@postgresql.org>
Subject: Re: Weird performance drop
Date: Thu, 29 Mar 2007 18:12:58 -0500
Message-ID: <018c01c77257$cb0bd520$2e00a8c0@tridecap.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Thread-Index: AcdyOWLSggCYqGnnRqy7EcFsnqarIgAHPTrw
In-Reply-To: <200703292056.08459.Vincenzo.Romano@gmail.com>
Importance: Normal

> From: pgsql-performance-owner@postgresql.org=20
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of=20
> Vincenzo Romano
>
> I thought that the query planner usually did a bad job on=20
> function bodies
> because they'd appear opaque to it.
> In this case it seems to me that the body is opaque only if I=20
> use the "like"
> operator.

If you run explain on a query that looks like "select * from a_table =
where
a_column like 'foo%'" (and you have the appropriate index) you will see =
that
postgres rewrites the where clause as "a_column >=3D 'foo' and a_column =
<
'fop'".  I think your problem is that the query is planned when the =
function
is created, and at that time postgres doesn't know the value you are
comparing against when you use the like operator, so postgres can't =
rewrite
the query using >=3D and <.  The problem doesn't happen for plain =
equality
because postgres doesn't need to know anything about what you are =
comparing
against in order to use equality.

Somebody else can correct me if I'm wrong.

Dave