From: Heikki Linnakangas <heikki@enterprisedb.com>
To: Arnaud Lesauvage <thewild@freesurf.fr>
Cc: pgsql-performance@postgresql.org
Subject: Re: Index on two columns not used
Date: Wed, 18 Oct 2006 14:57:25 +0100
Message-ID: <453632C5.50501@enterprisedb.com> (raw)
In-Reply-To: <45361A2D.6080702@freesurf.fr>
References: <45360E25.9010403@freesurf.fr>
<45361295.9050504@enterprisedb.com>
<45361A2D.6080702@freesurf.fr>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: heikki@enterprisedb.com, thewild@freesurf.fr
Subject: Re: Index on two columns not used
In-Reply-To: <453632C5.50501@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox