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
Date: Wed, 12 Nov 2008 06:54:05 -0700
Message-ID: <20081112135405.GD22032@polonium.part.net> (raw)
In-Reply-To: <003c01c944c9$c1d92ac0$458b8040$@com>
References: <gfcprb$m40$1@news.hub.org>
<4919F2C6.4000303@fuzzy.cz>
<003c01c944c9$c1d92ac0$458b8040$@com>
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
Attachments:
signature.asc (application/pgp-signature, 196B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: eggyknap@gmail.com, julien@squidsolutions.com
Subject: Re: Index usage with sub select or inner joins
In-Reply-To: <20081112135405.GD22032@polonium.part.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox