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
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.
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
pgsql-general by date
|Next:||From: Bruce Momjian||Date: 1999-04-23 16:14:53|
|Subject: Re: [GENERAL] entity relationship diagram free software|
|Previous:||From: Chairudin Sentosa||Date: 1999-04-23 09:13:12|
|Subject: Re: [INTERFACES] where did that date and time come from??|