Postgres Pro
Downloads
Home   >   mailing lists

Thread: Baffled by failure to use index when WHERE uses a function

Baffled by failure to use index when WHERE uses a function

From
Clive Page
Date:
I have a table cov3 of about 3 million rows, with a B-tree index on an
integer column called hpix.  If I do a simple select on this column it
works in milliseconds, using the index naturally:

select * from cov3 where hpixint = 482787587;
    hpix    |  expos  |  hpixint
-----------+---------+-----------
  482787587 | 30529.6 | 482787587
(1 row)

The problem is that I want to use a user-defined function called healpix
which returns a single integer value in my queries; the function details
are unlikely to be relevant (it selects a pixel from a celestial
position), but its definition is:

  \df healpix
                             List of functions
  Schema |  Name   | Result data type |        Argument data types
--------+---------+------------------+------------------------------------
  public | healpix | integer          | double precision, double precision

So I would like to use this function to find rows, and I try for example:

select * from cov3 where hpix = healpix(2.85,-11.48);

but it takes ages.  An EXPLAIN shows why, it insists upon a sequential
scan:

explain select * from cov3 where hpix = healpix(2.85,-11.48);
                                       QUERY PLAN
--------------------------------------------------------------------------------------
  Seq Scan on cov3  (cost=0.00..93046.81 rows=1 width=20)
    Filter: (hpix = (healpix(2.85::double precision, -11.48::double precision))::text)

Does anyone have any idea why, or know how I can restore adequate
performance?

I am using Postgres 8.1.0 on Linux.

--
Clive Page
Dept of Physics & Astronomy,
University of Leicester,
Leicester, LE1 7RH,  U.K.

Re: Baffled by failure to use index when WHERE uses a function

From
Martijn van Oosterhout
Date:
On Fri, Mar 10, 2006 at 09:14:27AM +0000, Clive Page wrote:
> I have a table cov3 of about 3 million rows, with a B-tree index on an
> integer column called hpix.  If I do a simple select on this column it
> works in milliseconds, using the index naturally:

<snip>

> So I would like to use this function to find rows, and I try for example:
>
> select * from cov3 where hpix = healpix(2.85,-11.48);
>
> but it takes ages.  An EXPLAIN shows why, it insists upon a sequential
> scan:

<snip>

You don't describe the exact structure of your table nor the exact
declaraion of your function, but is it possible your function is marked
VOLATILE rather tha STABLE or IMMUTABLE?

> I am using Postgres 8.1.0 on Linux.

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
Attachment

Re: Baffled by failure to use index when WHERE uses a function

From
Michael Fuhr
Date:
On Fri, Mar 10, 2006 at 09:14:27AM +0000, Clive Page wrote:
> I have a table cov3 of about 3 million rows, with a B-tree index on an
> integer column called hpix.  If I do a simple select on this column it
> works in milliseconds, using the index naturally:
>
> select * from cov3 where hpixint = 482787587;
                           ^^^^^^^
That's not the column you said you were using and that your other
example uses.  Have you verified that hpix has an index?

> So I would like to use this function to find rows, and I try for example:
>
> select * from cov3 where hpix = healpix(2.85,-11.48);
>
> but it takes ages.  An EXPLAIN shows why, it insists upon a sequential
> scan:

If healpix() always returns the same output for given input then
define it to be IMMUTABLE.  For more information see "Function
Volatility Categories" in the documentation.

http://www.postgresql.org/docs/8.1/interactive/xfunc-volatility.html

--
Michael Fuhr

Re: Baffled by failure to use index when WHERE uses a function

From
Richard Huxton
Date:
Clive Page wrote:
> I have a table cov3 of about 3 million rows, with a B-tree index on an
> integer column called hpix.  If I do a simple select on this column it
> works in milliseconds, using the index naturally:
>
> select * from cov3 where hpixint = 482787587;
>    hpix    |  expos  |  hpixint
> -----------+---------+-----------
>  482787587 | 30529.6 | 482787587
> (1 row)


This doesn't show any index being used. EXPLAIN ANALYSE would have.

> The problem is that I want to use a user-defined function called healpix
> which returns a single integer value in my queries; the function details
> are unlikely to be relevant (it selects a pixel from a celestial
> position), but its definition is:
>
>  \df healpix
>                             List of functions
>  Schema |  Name   | Result data type |        Argument data types
> --------+---------+------------------+------------------------------------
>  public | healpix | integer          | double precision, double precision

> select * from cov3 where hpix = healpix(2.85,-11.48);
>
> but it takes ages.  An EXPLAIN shows why, it insists upon a sequential
> scan:
>
> explain select * from cov3 where hpix = healpix(2.85,-11.48);
>                                       QUERY PLAN
> --------------------------------------------------------------------------------------
>
>  Seq Scan on cov3  (cost=0.00..93046.81 rows=1 width=20)
>    Filter: (hpix = (healpix(2.85::double precision, -11.48::double
> precision))::text)
>
> Does anyone have any idea why, or know how I can restore adequate
> performance?

Do you understand the difference between the IMMUTABLE,STABLE,VOLATILE
attributes for functions and what the difference between them is?

http://www.postgresql.org/docs/8.1/static/sql-createfunction.html

However, in the example above the real problem is that the query using
an index tests against "hpixint" whereas your function compares against
"hpix". Make sure you're testing against the same column, then post back.

--
   Richard Huxton
   Archonet Ltd

Re: Baffled by failure to use index when WHERE uses a

From
Clive Page
Date:
On Fri, 10 Mar 2006, Martijn van Oosterhout wrote:

> You don't describe the exact structure of your table nor the exact
> declaraion of your function, but is it possible your function is marked
> VOLATILE rather tha STABLE or IMMUTABLE?

Thanks for that hint - my function was not marked in any way, so I guess
it
got to be VOLATILE by default.  I have just marked it as IMMUTABLE and it
now uses the index as expected, with a huge performance gain.  I confess
that I was totally ignorant of the differences between these three types
of function.

Sorry I slightly messed up the cut/paste of my posting, I had been
experimenting with various versions of the same table and didn't quite get
the details consistent in what I posted.

Thanks also to Richard Huxton and Martin van Oosterhout who gave me the
same hint.

What an excellent support group this is.

--
Clive Page
Dept of Physics & Astronomy,
University of Leicester,
Leicester, LE1 7RH,  U.K.

Re: Baffled by failure to use index when WHERE uses a function

From
"Hongxi.Ma"
Date:
if ur function 'healpix' marked  'VOLATILE ' , it meas 'passed the same
params may result to diffrennt result', so , database have to compare the
value row by row (db does not know what u actully mean)
----- Original Message -----
From: "Clive Page" <cgp@star.le.ac.uk>
To: <pgsql-general@postgresql.org>
Sent: Friday, March 10, 2006 5:14 PM
Subject: [GENERAL] Baffled by failure to use index when WHERE uses a
function


> I have a table cov3 of about 3 million rows, with a B-tree index on an
> integer column called hpix.  If I do a simple select on this column it
> works in milliseconds, using the index naturally:
>
> select * from cov3 where hpixint = 482787587;
>     hpix    |  expos  |  hpixint
> -----------+---------+-----------
>   482787587 | 30529.6 | 482787587
> (1 row)
>
> The problem is that I want to use a user-defined function called healpix
> which returns a single integer value in my queries; the function details
> are unlikely to be relevant (it selects a pixel from a celestial
> position), but its definition is:
>
>   \df healpix
>                              List of functions
>   Schema |  Name   | Result data type |        Argument data types
> --------+---------+------------------+------------------------------------
>   public | healpix | integer          | double precision, double precision
>
> So I would like to use this function to find rows, and I try for example:
>
> select * from cov3 where hpix = healpix(2.85,-11.48);
>
> but it takes ages.  An EXPLAIN shows why, it insists upon a sequential
> scan:
>
> explain select * from cov3 where hpix = healpix(2.85,-11.48);
>                                        QUERY PLAN
> --------------------------------------------------------------------------
------------
>   Seq Scan on cov3  (cost=0.00..93046.81 rows=1 width=20)
>     Filter: (hpix = (healpix(2.85::double precision, -11.48::double
precision))::text)
>
> Does anyone have any idea why, or know how I can restore adequate
> performance?
>
> I am using Postgres 8.1.0 on Linux.
>
> --
> Clive Page
> Dept of Physics & Astronomy,
> University of Leicester,
> Leicester, LE1 7RH,  U.K.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly