Re: Index on two columns not used

От: Arnaud Lesauvage
Тема: Re: Index on two columns not used
Дата: ,
Msg-id: 45361A2D.6080702@freesurf.fr
(см: обсуждение, исходный текст)
Ответ на: Re: Index on two columns not used  ("Heikki Linnakangas")
Ответы: Re: Index on two columns not used  ("Heikki Linnakangas")
Список: pgsql-performance

Скрыть дерево обсуждения

Index on two columns not used  (Arnaud Lesauvage, )
 Re: Index on two columns not used  ("Heikki Linnakangas", )
  Re: Index on two columns not used  (Arnaud Lesauvage, )
   Re: Index on two columns not used  ("Heikki Linnakangas", )
    Re: Index on two columns not used  (Arnaud Lesauvage, )
     Re: Index on two columns not used  ("Heikki Linnakangas", )
      Re: Index on two columns not used  (Arnaud Lesauvage, )
  Re: Index on two columns not used  (Péter Kovács, )
   Re: Index on two columns not used  (Markus Schaber, )
    Re: Index on two columns not used  (Alvaro Herrera, )
     Re: Index on two columns not used  (Markus Schaber, )
      Re: Index on two columns not used  (Tom Lane, )
       Re: Index on two columns not used  (Markus Schaber, )
    Re: Index on two columns not used  (Péter Kovács, )
 Re: Index on two columns not used  (Tom Lane, )
  Re: Index on two columns not used  (Arnaud Lesauvage, )

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



В списке pgsql-performance по дате сообщения:

От: Arnaud Lesauvage
Дата:
Сообщение: Re: Index on two columns not used
От: "Jim C. Nasby"
Дата:
Сообщение: Re: Performance Optimization for Dummies 2 - the SQL