Baffled by failure to use index when WHERE uses a function
От | Clive Page |
---|---|
Тема | Baffled by failure to use index when WHERE uses a function |
Дата | |
Msg-id | Pine.LNX.4.63.0603100905230.15119@peneca.star.le.ac.uk обсуждение исходный текст |
Ответы |
Re: Baffled by failure to use index when WHERE uses a function
Re: Baffled by failure to use index when WHERE uses a function Re: Baffled by failure to use index when WHERE uses a function |
Список | 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.
В списке pgsql-general по дате отправления: