Baffled by failure to use index when WHERE uses a function

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

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/

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

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.

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)