Re: Baffled by failure to use index when WHERE uses a function - Mailing list pgsql-general

From Hongxi.Ma
Subject Re: Baffled by failure to use index when WHERE uses a function
Date
Msg-id 007601c644b5$fe107aa0$c70000c0@mhx
Whole thread Raw
In response to Baffled by failure to use index when WHERE uses a function  (Clive Page <cgp@star.le.ac.uk>)
List pgsql-general
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@star.le.ac.uk>
To: <pgsql-general@postgresql.org>
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@postgresql.org so that your
>        message can get through to the mailing list cleanly


pgsql-general by date:

Previous
From: "Guy Rouillier"
Date:
Subject: Re: NULL TIMESTAM problem
Next
From: "AKHILESH GUPTA"
Date:
Subject: input from a external text file......!