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@xxxxxxxxxxxxx> To: <pgsql-general@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx so that your > message can get through to the mailing list cleanly
