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

Bruce Momjian wrote on Tue, 06 Jul 1999 23:24:12 EDT
>> Not forcing float4 conversion does much better, e.g.
>> 
>>    select count(*) from mydata where x>3.4 and x<3.5;
>> 
>
>OK, let me ask.  Vacuum analyze.  What does pg_statistics show for
>min/max values?  What does EXPLAIN show?
>

The _true_ variable name is called "j_m" in the table "lmctot" and 
the database is called lmc.

From "select * from pg_statistic", I have min and max to be 
2.731 and 99.999 for that float4 field.

Explain for the converted values:
--------------------------------
lmc=> explain select count(*) from lmctot where j_m>'3.4' and j_m<'3.5';
NOTICE:  QUERY PLAN:

Aggregate  (cost=62349.97 rows=788100 width=4)
  ->  Index Scan using j on lmctot  (cost=62349.97 rows=788100 width=4)

And for the uncast values:
-------------------------

lmc=> explain select count(*) from lmctot where j_m>'3.4' and j_m<'3.5';
NOTICE:  QUERY PLAN:

Aggregate  (cost=62349.97 rows=788100 width=4)
  ->  Index Scan using j on lmctot  (cost=62349.97 rows=788100 width=4)


lmc=> explain select count(*) from lmctot where j_m>3.4 and
j_m<3.5;NOTICE:  QUERY PLAN:

Aggregate  (cost=423901.50 rows=788100 width=4)
  ->  Seq Scan on lmctot  (cost=423901.50 rows=788100 width=4)

EXPLAIN


Any clues?  I tried looking at the "verbose" output but that
is beyond me. 

I appreciate the help.  I need to figure out whether pgsql
will do the job for this application.

--M

===========================================================================

Martin Weinberg                      Phone: (413) 545-3821
Dept. of Physics and Astronomy       FAX:   (413) 545-2117/0648
530 Graduate Research Tower
University of Massachusetts
Amherst, MA  01003-4525