Re: [GENERAL] Problems with inequalities on numeric fields in 6.5

> Thanks, Bruce!
>
> Yes, I tried the latter query and it's the same:
>
> --------------------------------------------------
>
> lmc=> explain select count(*) from lmctot where j_m>3.4::float4 and j_m<3.5::float4;
> NOTICE: QUERY PLAN:
>
> Aggregate (cost=62349.97 rows=788100 width=4)
> -> Index Scan using j on lmctot (cost=62349.97 rows=788100 width=4)
>
> EXPLAIN
>
> --------------------------------------------------
> I've tried all permutations of the conversions in the ranges with
> similar results (and vacuum analyzed several times as well as
> dumped and reloaded and reloaded from scracth). We have
> a larger database with 20M rows which has a similar behavior.
>
> There are 7092894 rows in database "lmc". So:
>
> (3.5-3.4)/(99.999-2.731) = 7292.1 != 788k
>
> A clue?

I have just fixed a problem with index size estimates. Try adding
#include <math.h> to the top of backend/optimizer/util/plancat.c. That
may fix the estimated number of tuples returned. However, it don't
think you are going to get better performance, since you are already
using the index in the above case. The only big win I can think of is
to use CLUSTER on that field. That should speed things up quite a bit.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026