Thread: Problem with an indexing on a large table. Suggestions needed.
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
Re: [GENERAL] Problem with an indexing on a large table. Suggestions needed.
From
Bruce Momjian
Date:
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