Problem with an indexing on a large table. Suggestions needed.

Can you try 6.5 beta when you can and let us know. Thomas, is this
going to work better in 6.5beta?

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

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026