Folks,
No one on general or novice have had any insight into the following
problem.
I have a very large table (10Gb, 20 million records each with 54 fields)
with both float, integer and text values. It's an astronomical
database for the 2MASS project (http://pegasus.phast.umass.edu). This
database will be 10 times larger in the end . . .
Anyway, if I submit a query such as:
select * from mytable where x=3.14 and y=6.28;
it takes about 3 minutes to return the record. Both x and y are indexed:
create index xindex on mytable using btree (x);create index yindex on mytable using btree (y);
And "explain" on the select query above says it's doing a sequential scan.
However if I say:
select * from mytable where x='3.14'::float4 and y='6.28'::float4;
it takes about 3 seconds! And now "explain" says it's doing an indexed
scan.
My understanding is that the query optimizer should pick the index
scan for this query based on the cost. My attempts at debugging
have not turned up anything obvious to me.
Is there a problem with my set up or is this a known problem? Is
there something I can do as a work around to make this efficient?
I would like PostgreSQL to succeed in this application, if possible,
so that it can be adopted. If we can get this working, I would
recommend that the astronomical community consider adopting this
PostgreSQL to "spin" portions of this database (if you think this
is reasonable).
BTW, this is PostgreSQL 6.4.2 on a dual Xeon running Linux 2.2.5,
over Debian 2.1.
Thanks!
--Martin
===========================================================================
Prof. 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
