Received: from svana.org (svana.org [210.9.66.30]) by postgresql.org (Postfix) with ESMTP id 16C2447613F for ; Wed, 13 Mar 2002 21:16:37 -0500 (EST) Received: from kleptog by svana.org with local (Exim 3.12 #1 (Debian)) id 16lKn9-00015z-00; Thu, 14 Mar 2002 13:16:31 +1100 Date: Thu, 14 Mar 2002 13:16:31 +1100 From: Martijn van Oosterhout To: Brian Knox Cc: pgsql-general@postgresql.org Subject: Re: Strange Postgresql Indexing Behavior Message-ID: <20020314131631.A3954@svana.org> Reply-To: Martijn van Oosterhout Mail-Followup-To: Brian Knox , pgsql-general@postgresql.org References: <19463.1014409239@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5i In-Reply-To: ; from laotse@aol.net on Wed, Mar 13, 2002 at 04:28:00PM -0500 X-Archive-Number: 200203/732 X-Sequence-Number: 22105 On Wed, Mar 13, 2002 at 04:28:00PM -0500, Brian Knox wrote: > > I'm having an interesting time trying to figure out some behavior with > postgresql indexes that I am trying to understand. [snip] > I did more queries and confirmed that when the number of rows returned is > below a certain number (I don't have enough data to determine the exact > number) the index is used, and when it is above a certain number, it is > not used. > > Can anyone explain to me what is happening / why it is happening / how to > make the indexes work correctly? Well, checking a tuple from an index is more expensive than checking a tuple from a sequential scan. So, if you want to select 50% of the table, it's faster to read the whole table than it is to use the index. The planner tries to guess where the break-even point is. Above, seq scan, below index scan. This is a FAQ, IIRC. HTH, -- Martijn van Oosterhout http://svana.org/kleptog/ > 11/9/2001 - a new beginning or the beginning of the end?