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
