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]

 



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



[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