From: Tom Lane <tgl@sss.pgh.pa.us>
To: Eyal Wilde <eyal@impactsoft.co.il>
Cc: pgsql-performance@postgresql.org
Cc: erans@impactsoft.co.il
Subject: Re: index not being used when variable is sent
Date: Tue, 16 Aug 2011 09:40:58 -0400
Message-ID: <25601.1313502058@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMiEbcinfF-WE1=kwCnHGx5Uth8po5GF24ukgNWvDzKVsifn+A@mail.gmail.com>
References: <CAMiEbcinfF-WE1=kwCnHGx5Uth8po5GF24ukgNWvDzKVsifn+A@mail.gmail.com>
Eyal Wilde <eyal@impactsoft.co.il> writes:
> CREATE OR REPLACE FUNCTION test_func(STR text)
> ...
> perform t1.val FROM t1 WHERE
> (COALESCE(rpad(t1.val, 100),'') ) like COALESCE(STR || '%','')
> order by COALESCE(rpad(t1.val, 100), '') using ~<~ LIMIT 5;
[ doesn't use index ]
No, it doesn't. The LIKE index optimization requires the LIKE pattern
to be a constant at plan time, so that the planner can extract the
pattern's fixed prefix. An expression depending on a function parameter
is certainly not constant.
If you really need this to work, you could use EXECUTE USING so that
the query is re-planned for each execution.
regards, tom lane
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: tgl@sss.pgh.pa.us, eyal@impactsoft.co.il, erans@impactsoft.co.il
Subject: Re: index not being used when variable is sent
In-Reply-To: <25601.1313502058@sss.pgh.pa.us>
* 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