Thread: Query in function not using index...

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
entityto whom they are addressed. If you have received this email in error please notify the sender. This message
containsconfidential information and is intended only for the individual named. If you are not the named addressee you
shouldnot disseminate, distribute or copy this e-mail. 

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

    Yeah.
    'wynn%' doesn't start with % so LIKE uses index.
    But Postgres doesn't know that you know that the contents of this
variable never starts with '%'...


>
> Thanks,
>
> John
>

Listmail <lists@peufeu.com> writes:
>> Any idea why using a variable v_streetName instead of a string 'wynn%'
>> behaves differently?

>     Yeah.
>     'wynn%' doesn't start with % so LIKE uses index.
>     But Postgres doesn't know that you know that the contents of this
> variable never starts with '%'...

Even more to the point: the transformation of LIKE 'pattern' into an
index range is a plan-time optimization, so if the planner hasn't got a
constant pattern to work with, it'll never happen.  (Yeah, this should
be improved someday.)

If you need to depend on LIKE indexing with non-constant patterns,
you'll have to use EXECUTE to force the query to be replanned each time.

            regards, tom lane

Tom,
  We were looking at the explain results and noticed that it was converting
the 'wynn%' into fname >= 'wynn' and fname < 'wyno'

  So we modified our function to mimic this transformation, where we have
two variables that contain the 'wynn' and 'wyno':
    Fname >= @var1 and fname < @var2

  This seems to pick up the index ok, but is this a valid and reliable
solution?  Or is there another/better way to do this?

Thanks for the explanation, it really helps.

John

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, April 27, 2007 5:57 PM
To: Listmail
Cc: John Cole; 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] Query in function not using index...

Listmail <lists@peufeu.com> writes:
>> Any idea why using a variable v_streetName instead of a string 'wynn%'
>> behaves differently?

>     Yeah.
>     'wynn%' doesn't start with % so LIKE uses index.
>     But Postgres doesn't know that you know that the contents of this
> variable never starts with '%'...

Even more to the point: the transformation of LIKE 'pattern' into an
index range is a plan-time optimization, so if the planner hasn't got a
constant pattern to work with, it'll never happen.  (Yeah, this should
be improved someday.)

If you need to depend on LIKE indexing with non-constant patterns,
you'll have to use EXECUTE to force the query to be replanned each time.

            regards, tom lane

--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.446 / Virus Database: 269.5.10/774 - Release Date: 4/23/2007
5:26 PM


--
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
entityto whom they are addressed. If you have received this email in error please notify the sender. This message
containsconfidential information and is intended only for the individual named. If you are not the named addressee you
shouldnot disseminate, distribute or copy this e-mail. 

> Tom,
>   We were looking at the explain results and noticed that it was
> converting
> the 'wynn%' into fname >= 'wynn' and fname < 'wyno'

    Does this also work if the last character is a unicode character ?

Listmail <lists@peufeu.com> writes:
>> We were looking at the explain results and noticed that it was
>> converting
>> the 'wynn%' into fname >= 'wynn' and fname < 'wyno'

>     Does this also work if the last character is a unicode character ?

In C locale it does, though you have to be careful to avoid generating
an invalid encoding.

            regards, tom lane