From: | Clive Page <cgp(at)star(dot)le(dot)ac(dot)uk> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Baffled by failure to use index when WHERE uses a function |
Date: | 2006-03-10 09:14:27 |
Message-ID: | Pine.LNX.4.63.0603100905230.15119@peneca.star.le.ac.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2006-03-10 09:31:17 | Re: Storage Estimates |
Previous Message | ycrux | 2006-03-10 09:11:40 | Re: PL/pgSQL question |