Message-ID: <4539F4E1.7000003@chemaxon.hu>
Date: Sat, 21 Oct 2006 12:22:25 +0200
From: =?ISO-8859-1?Q?P=E9ter_Kov=E1cs?= <peter.kovacs@chemaxon.hu>
User-Agent: Thunderbird 1.5.0.7 (Windows/20060909)
MIME-Version: 1.0
To: Heikki Linnakangas <heikki@enterprisedb.com>
Cc: Arnaud Lesauvage <thewild@freesurf.fr>,
	pgsql-performance@postgresql.org
Subject: Re: Index on two columns not used
References: <45360E25.9010403@freesurf.fr> <45361295.9050504@enterprisedb.com>
In-Reply-To: <45361295.9050504@enterprisedb.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Sorry for the amateurish question, but what are "heap tuples"?

Also, my understanding is that the following statement applies only for 
composite indexes: "PostgreSQL can't use the values stored in the index 
to check the join condition". I assume that PostgreSQL will be able to 
use single-column-indexes for join conditions. Is this correct?

Thank you,
Peter

Heikki Linnakangas wrote:
> 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 ?
>>
>> ALTER TABLE geo.subcities_names
>>   ADD CONSTRAINT subcities_names_pkey PRIMARY KEY(subcity_gid, 
>> language_id);
>>
>> CREATE INDEX subcities_gid_language_id
>>   ON geo.subcities
>>   USING btree
>>   (gid, official_language_id);
>>
>> EXPLAIN ANALYZE
>> SELECT * FROM geo.subcities sc, geo.subcities_names scn
>> WHERE sc.gid = scn.subcity_gid AND sc.official_language_id = 
>> scn.language_id;
>
> My theory:
>
> There's no additional restrictions besides the join condition, so the 
> system has to scan both tables completely. It chooses to use a full 
> index scan instead of a seq scan to be able to do a merge join. 
> Because it's going to have to scan the indexes completely anyway, it 
> chooses the smallest index which is subcities_pkey.
>
> 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.
>