Search Postgresql Archives

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

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

 



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


[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