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