Search Postgresql Archives

Baffled by failure to use index when WHERE uses a function

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux