Skip site navigation (1) Skip section navigation (2)

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

From: Martin Weinberg <weinberg(at)osprey(dot)phast(dot)umass(dot)edu>
To: pgsql-general(at)postgresql(dot)org
Subject: Problem with an indexing on a large table. Suggestions needed.
Date: 1999-04-23 13:41:45
Message-ID: 199904231341.JAA08915@osprey.phast.umass.edu (view raw or flat)
Thread:
Lists: 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

Responses

pgsql-general by date

Next:From: Bruce MomjianDate: 1999-04-23 16:14:53
Subject: Re: [GENERAL] entity relationship diagram free software
Previous:From: Chairudin SentosaDate: 1999-04-23 09:13:12
Subject: Re: [INTERFACES] where did that date and time come from??

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group