Query in function not using index...

[John Cole <john.cole@xxxxxxx>]

Hello,
  I need some help with a query inside a function.  The table has 31M rows
in it (us streets), and a select statement takes 10ms when using an index
built for the query...

  But, when the same query is in a function and uses a variable the index
isn't used and the query takes a few minutes.

Here is the query:

DECLARE
  v_cntr INTEGER;
  v_streetName VARCHAR := 'wynn%';
  v_addr INTEGER := 307;
BEGIN

  SELECT count(0) into v_cntr FROM tiger_geocode_roads r
	  left join placecu p on r.place=p.placei
	  join state_lookup s on r.state=s.fips
	  WHERE lower(r.fename::text) like 'wynn%'
	      AND r.gadd >= v_addr
	      AND r.ladd <= v_addr
	      AND fename IS NOT NULL 
	      AND gadd IS NOT NULL 
	      AND ladd IS NOT NULL;

This version comes back in 10ms, but the following takes several minutes:

DECLARE
  v_cntr INTEGER;
  v_streetName VARCHAR := 'wynn%';
  v_addr INTEGER := 307;
BEGIN

  SELECT count(0) into v_cntr FROM tiger_geocode_roads r
	  left join placecu p on r.place=p.placei
	  join state_lookup s on r.state=s.fips
	  WHERE lower(r.fename::text) like v_streetName
	      AND r.gadd >= v_addr
	      AND r.ladd <= v_addr
	      AND fename IS NOT NULL 
	      AND gadd IS NOT NULL 
	      AND ladd IS NOT NULL;

Our index looks like:

CREATE INDEX tiger_geocode_roads_lower_fename_addr
  ON tiger_geocode_roads
  USING btree
  (lower(fename::text), gadd, ladd)
  WHERE fename IS NOT NULL AND gadd IS NOT NULL AND ladd IS NOT NULL;

Any idea why using a variable v_streetName instead of a string 'wynn%'
behaves differently?

Thanks,

John

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 269.5.10/774 - Release Date: 4/23/2007
5:26 PM
 
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.