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