DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=date:from:to:cc:subject:message-id:references:mime-version
	:content-type:content-disposition:in-reply-to:user-agent;
	b=YbNwyivX6n4mHuDeR8wCwf58eQRPAk/uDTb5tBBuq8xkfMxKvT5WoqJSB/zbSObxiY
	8Sjf+PGKrVNFD/tmkF1FlDkLsEAQAw4eckOQYIELIq7SjX4pwAy0+2WhWduSHV8X+42+
	f4bOC7xoO3xHp9eRfH+us0xt7Jf9D8owpuyEE=
Date: Wed, 12 Nov 2008 06:54:05 -0700
From: Joshua Tolley <eggyknap@gmail.com>
To: Julien Theulier <julien@squidsolutions.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index usage with sub select or inner joins
Message-ID: <20081112135405.GD22032@polonium.part.net>
References: <gfcprb$m40$1@news.hub.org> <4919F2C6.4000303@fuzzy.cz>
	<003c01c944c9$c1d92ac0$458b8040$@com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="wULyF7TL5taEdwHz"
Content-Disposition: inline
In-Reply-To: <003c01c944c9$c1d92ac0$458b8040$@com>
User-Agent: Mutt/1.5.18 (2008-05-17)


--wULyF7TL5taEdwHz
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Wed, Nov 12, 2008 at 02:22:47PM +0100, Julien Theulier wrote:
> QUESTION: Why the planner choose seq scan in the first case & indexes scan
> in the second case? In a more general way, I observed that the planner has
> difficulties to select index scans & does in almost all the cases seq scan,
> when doing join queries. After investigations, it looks like when you join
> table a with table b on a column x and y and you have an index on column x
> only, the planner is not able to choose the index scan. You have to build
> the index corresponding exactly to the join statement btw the 2 tables

Short, general answer: index scans aren't always faster than sequential
scans, and the planner is smart enough to know that. Googling "Why isn't
postgresql using my index" provides more detailed results, but in short,
if it scans an index, it has to read pages from the index, and for all
the tuples it finds in the index, it has to read once again from the
heap, whereas a sequential scan requires reading once from the heap. If
your query will visit most of the rows of the table, pgsql will choose a
sequential scan over an index scan.

- Josh / eggyknap

--wULyF7TL5taEdwHz
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkka3/0ACgkQRiRfCGf1UMPMqQCdG0+zZcjdt6p5mabrpmiLFGpb
GooAoI+LM6DwlyZ6QJmCA0gUBregk6tX
=Qbui
-----END PGP SIGNATURE-----

--wULyF7TL5taEdwHz--