Folks,
I have a very large table (10Gb, 20 million records each with 54 fields)
with both float, integer and text values. 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 know to pick
the index scan for this query. Is there a problem with my set up?
Is there something I can do to make this work efficiently? Did I
set up my indices incorrectly?
BTW, this is PostgreSQL 6.4.2 on a dual Xeon running Linux 2.2.5.
Thanks!
--Martin
===========================================================================
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