Re: Index on two columns not used

From: Arnaud Lesauvage
Subject: Re: Index on two columns not used
Date: ,
Msg-id: 4536698B.7010001@freesurf.fr
(view: Whole thread, Raw)
In response to: Re: Index on two columns not used  (Tom Lane)
List: pgsql-performance

Tree view

Index on two columns not used  (Arnaud Lesauvage, )
 Re: Index on two columns not used  ("Heikki Linnakangas", )
  Re: Index on two columns not used  (Arnaud Lesauvage, )
   Re: Index on two columns not used  ("Heikki Linnakangas", )
    Re: Index on two columns not used  (Arnaud Lesauvage, )
     Re: Index on two columns not used  ("Heikki Linnakangas", )
      Re: Index on two columns not used  (Arnaud Lesauvage, )
  Re: Index on two columns not used  (Péter Kovács, )
   Re: Index on two columns not used  (Markus Schaber, )
    Re: Index on two columns not used  (Alvaro Herrera, )
     Re: Index on two columns not used  (Markus Schaber, )
      Re: Index on two columns not used  (Tom Lane, )
       Re: Index on two columns not used  (Markus Schaber, )
    Re: Index on two columns not used  (Péter Kovács, )
 Re: Index on two columns not used  (Tom Lane, )
  Re: Index on two columns not used  (Arnaud Lesauvage, )

Tom Lane a écrit :
> Arnaud Lesauvage <> writes:
>> 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 ?
>
> Hm, is gid by itself nearly unique in these tables?  If so, the merge
> join would get only marginally more efficient by using both columns as
> merge conditions.  Heikki's probably right to guess that the planner
> thinks it's better to use the smaller index.
>
> However, if there are lots of duplicate gids, then it ought to have
> preferred the two-column merge ...

gid is the primary key of the first table, so absolutely unique.
Thanks for the information Tom !

--
Arnaud


pgsql-performance by date:

From: Jeff Davis
Date:
Subject: Re: Postgresql 8.1.4 - performance issues for select on
From: Mark Kirkwood
Date:
Subject: Re: measuring shared memory usage on Windows