From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: David Gagnon <dgagnon@siunik.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why the planner is not using the INDEX .
Date: Tue, 5 Jul 2005 07:02:07 -0700 (PDT)
Message-ID: <20050705065351.W63027@megazone.bigpanda.com> (raw)
In-Reply-To: <42C9D47E.7090103@siunik.com>
References: <20050630222451.GA17628@calvin.surfutopia.net>
<42C473BA.9090305@commandprompt.com>
<20050630235821.GA2163@calvin.surfutopia.net>
<42C994BD.3000909@siunik.com>
<20050704132446.O14579@megazone.bigpanda.com>
<42C9D47E.7090103@siunik.com>
On Mon, 4 Jul 2005, David Gagnon wrote:
> Thanks .. I miss that FK don't create indexed ... since Primary key
> implicitly does ...
>
> I'm a bit surprised of that behavior thought, since it means that if we
> delete a row from table A all tables (B,C,D) with FK pointing to this
> table (A) must be scanned.
> If there is no index on those tables it means we gone do all Sequantial
> scans. Than can cause significant performance problem!!!.
>
> Is there a reason why implicit index aren't created when FK are
> declared. I looked into the documentation and I haven't found a way to
The reason is that it's not always useful to have an index for that
purpose. You could either have low selectivity (in which case the index
wouldn't be used) or low/batch changes to the referenced table (in which
case the cost of maintaining the index may be greater than the value of
having the index) or other such cases. In primary key and unique, we
currently have no choice but to make an index because that's how the
constraint is currently implemented.
> tell postgresql to automatically create an index when creating la FK.
> Does it means I need to manage it EXPLICITLY with create index statement
> ?
Yeah.
>Is there another way ?
Not that I can think of without changing the source.
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: sszabo@megazone.bigpanda.com, dgagnon@siunik.com
Subject: Re: Why the planner is not using the INDEX .
In-Reply-To: <20050705065351.W63027@megazone.bigpanda.com>
* 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