thread-index: AcbyvWFl2uDJe4DgRn2ATQ4yIsOLIw==
Message-ID: <453632C5.50501@enterprisedb.com>
Content-Class: urn:content-classes:message
Importance: normal
Date: Wed, 18 Oct 2006 14:57:25 +0100
From: "Heikki Linnakangas" <heikki@enterprisedb.com>
User-Agent: Thunderbird 1.5.0.7 (X11/20060915)
MIME-Version: 1.0
To: "Arnaud Lesauvage" <thewild@freesurf.fr>
Cc: <pgsql-performance@postgresql.org>
Subject: Re: Index on two columns not used
References: <45360E25.9010403@freesurf.fr> <45361295.9050504@enterprisedb.com>
	<45361A2D.6080702@freesurf.fr>
In-Reply-To: <45361A2D.6080702@freesurf.fr>
Content-Type: text/plain;
	format=flowed;
	charset="ISO-8859-1"
Content-Transfer-Encoding: 7bit

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