Re: Index on two columns not used

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