Index oddity - Mailing list pgsql-performance

I'm having a performance issue that I just can't resolve and its very,
very curious.  Thought someone here might be able to shed some light on
the subject.

I'm using Postgres 7.4.2 on Red Hat 9.  I have a table with 763,809 rows
in it defined as follows ...

ksedb=# \d nrgfeature
                Table "public.nrgfeature"
     Column     |            Type             | Modifiers
----------------+-----------------------------+-----------
 fid1           | numeric(64,0)               | not null
 fid2           | numeric(64,0)               | not null
 created        | timestamp without time zone | not null
 createdby      | character varying(30)       | not null
 modified       | timestamp without time zone |
 modifiedby     | character varying(30)       |
 geommodified   | timestamp without time zone |
 geommodifiedby | character varying(30)       |
 deleted        | timestamp without time zone |
 deletedby      | character varying(30)       |
 featuretypeid  | smallint                    | not null
 description    | text                        |
 datasourceid   | smallint                    | not null
 lowerleftx     | double precision            | not null
 lowerlefty     | double precision            | not null
 upperrightx    | double precision            | not null
 upperrighty    | double precision            | not null
 diagonalsize   | double precision            |
 login          | character varying(25)       |
Indexes:
    "nrgfeature_pkey" primary key, btree (fid1, fid2)
    "nrgfeature_ft_index" btree (featuretypeid)
    "nrgfeature_xys_index" btree (upperrightx, lowerleftx, upperrighty,
lowerlefty, diagonalsize)
Inherits: commonfidattrs,
          commonrevisionattrs


... If I write a query as follows ...

SELECT *
FROM nrgfeature f
WHERE
    upperRightX > 321264.23697721504
    AND lowerLeftX < 324046.79981208267
    AND upperRightY > 123286.26189863647
    AND lowerLeftY < 124985.92745047594
    AND diagonalSize > 50.000
;

... (or any value for diagonalsize over 50) then my query runs in 50-100
milliseconds.  However, if the diagonalSize value is changed to 49.999
or any value below 50, then the query takes over a second for a factor
of 10 degradation in speed, even though the exact same number of rows is
returned.

The query plan for diagonalSize > 50.000 is ...

Index Scan using nrgfeature_xys_index on nrgfeature f
(cost=0.00..17395.79 rows=4618 width=220)
   Index Cond: ((upperrightx > 321264.236977215::double precision) AND
(lowerleftx < 324046.799812083::double precision) AND (upperrighty >
123286.261898636::double precision) AND (lowerlefty <
124985.927450476::double precision) AND (diagonalsize > 50::double
precision))

... while for diagonalSize > 49.999 is ...

 Seq Scan on nrgfeature f  (cost=0.00..31954.70 rows=18732 width=220)
   Filter: ((upperrightx > 321264.236977215::double precision) AND
(lowerleftx < 324046.799812083::double precision) AND (upperrighty >
123286.261898636::double precision) AND (lowerlefty <
124985.927450476::double precision) AND (diagonalsize > 49.999::double
precision))

... and yes, if I set enable_seqscan=false then the index is forced to
be used.  However, despite this being an undesirable solution for this
simple case it doesn't solve the problem for the general case.  As soon
as I add in joins with a couple of tables to perform the actual query I
want to perform, the seq scan setting doesn't force the index to be used
anymore.  Instead, the primary key index is used at this same
diagonalSize cutoff and the 5-part double precision clause is used as a
filter to the index scan and the result is again a very slow query.

I can provide those queries and results but that would only complicate
this already lengthy email and the above seems to be the crux of the
problem anyway.

Any help or thoughts would be greatly appreciated of course.

Thanks,

Ken Southerland


--
------s----a----m----s----i----x----e----d----d------
--

Ken Southerland
Senior Consultant
Sam Six EDD
http://www.samsixedd.com

503-236-4288 (office)
503-358-6542 (cell)