Message-ID: <45361A2D.6080702@freesurf.fr>
Date: Wed, 18 Oct 2006 14:12:29 +0200
From: Arnaud Lesauvage <thewild@freesurf.fr>
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: Heikki Linnakangas <heikki@enterprisedb.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Index on two columns not used
References: <45360E25.9010403@freesurf.fr> <45361295.9050504@enterprisedb.com>
In-Reply-To: <45361295.9050504@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

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