Re: Index on two columns not used

Arnaud Lesauvage wrote:
> 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)

Well, if no-one is using the index, it is useless..

> 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)

It depends. I would advise not to denormalize unless you really really
have to. It's hard to say without more knowledge of the application.

Is the query you showed a typical one? It ran in about 160 ms, is that
good enough? It's doesn't sound too bad, considering that it returned
almost 40000 rows.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com