Re: Index on two columns not used

From: Tom Lane
Subject: Re: Index on two columns not used
Date: ,
Msg-id: 28276.1161192717@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Index on two columns not used  (Arnaud Lesauvage)
Responses: Re: Index on two columns not used  (Arnaud Lesauvage)
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, )

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 ...

            regards, tom lane


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