Index on two columns not used

От: Arnaud Lesauvage
Тема: Index on two columns not used
Дата: ,
Msg-id: 45360E25.9010403@freesurf.fr
(см: обсуждение, исходный текст)
Ответы: Re: Index on two columns not used  ("Heikki Linnakangas")
Re: Index on two columns not used  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

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, )

Hi list !

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;

Result :

  Merge Join  (cost=0.00..4867.91 rows=37917 width=240) (actual
time=0.037..149.022 rows=39323 loops=1)
    Merge Cond: ("outer".gid = "inner".subcity_gid)
    Join Filter: ("outer".official_language_id = "inner".language_id)
    ->  Index Scan using subcities_pkey on subcities sc
(cost=0.00..1893.19 rows=39357 width=200) (actual time=0.015..43.430
rows=39357 loops=1)
    ->  Index Scan using subcities_names_pkey on subcities_names scn
(cost=0.00..2269.39 rows=40517 width=40) (actual time=0.012..35.465
rows=40517 loops=1)
  Total runtime: 157.389 ms
(6 rows)


Thanks for your suggestions !
Regards
--
Arnaud


В списке pgsql-performance по дате сообщения:

От: Robert Treat
Дата:
Сообщение: Re: Hints proposal
От: Tom Lane
Дата:
Сообщение: Re: Index on two columns not used