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
