Problem with an indexing on a large table. Suggestions needed. - Mailing list pgsql-general

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