Re: [GENERAL] 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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026