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

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(at)star(dot)le(dot)ac(dot)uk>
To: <pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly