From: Arnaud Lesauvage <thewild@freesurf.fr>
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index on two columns not used
Date: Wed, 18 Oct 2006 14:12:29 +0200
Message-ID: <45361A2D.6080702@freesurf.fr> (raw)
In-Reply-To: <45361295.9050504@enterprisedb.com>
References: <45360E25.9010403@freesurf.fr>
<45361295.9050504@enterprisedb.com>
Heikki Linnakangas a écrit :
> Arnaud Lesauvage wrote:
>> I have two table with a 2-column index on both of them.
>> In the first table, the first colum of the index is the primary key, the
>> second one is an integer field.
>> In the second table, the two columns are the primary key.
>> When I join these two tables, the 2-column index of the first table is
>> not used.
>> Why does the query planner think that this plan is better ?
>
> You'd think that the system could do the merge using just the indexes,
> and only fetch the heap tuples for matches. If that were the case, using
> the 2-column index would indeed be a good idea. However, PostgreSQL
> can't use the values stored in the index to check the join condition, so
> all the heap tuples are fetched anyway. There was just recently
> discussion about this on this list:
> http://archives.postgresql.org/pgsql-performance/2006-09/msg00080.php.
>
Thanks for your answer Heikki.
I did not know that joins were not using index values, and
that PostgreSQL had to fecth the heap tuples anyway.
Does this mean that this 2-column index is useless ? (I
created it for the join, I don't often filter on both
columns otherwise)
This query was taken from my "adminsitrative areas" model
(continents, countries, etc...). Whenever I query this
model, I have to join many tables.
I don't really know what the overhead of reading the
heap-tuples is, but would it be a good idea to add
data-redundancy in my tables to avoid joins ? (adding
country_id, continent_id, etc... in the "cities" table)
Regards
--
Arnaud
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: thewild@freesurf.fr, heikki@enterprisedb.com
Subject: Re: Index on two columns not used
In-Reply-To: <45361A2D.6080702@freesurf.fr>
* 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