Re: force the use of a particular index

От: Rod Taylor
Тема: Re: force the use of a particular index
Дата: ,
Msg-id: 1057940450.65544.14.camel@jester
(см: обсуждение, исходный текст)
Ответ на: Re: force the use of a particular index  (Scott Cain)
Список: pgsql-performance

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

force the use of a particular index  (Scott Cain, )
 Re: force the use of a particular index  (Rod Taylor, )
  Re: force the use of a particular index  (Scott Cain, )
   Re: force the use of a particular index  (Rod Taylor, )
 Re: force the use of a particular index  (Rod Taylor, )
  Re: force the use of a particular index  (Scott Cain, )
 Re: force the use of a particular index  (Scott Cain, )
  Re: force the use of a particular index  (Rod Taylor, )
   Re: force the use of a particular index  (Scott Cain, )
 Re: force the use of a particular index  (Tom Lane, )
  Re: force the use of a particular index  (Scott Cain, )
   Re: force the use of a particular index  (Tom Lane, )

On Fri, 2003-07-11 at 11:36, Scott Cain wrote:
> Rod,
>
> I see what you mean about the scan on the feature_pkey taking a long
> time.  I tried several things to remedy that.  I created an index on
> feature (feature_id,type_id) (which I don't think makes sense since
> feature_id is the primary key, so add another column really doesn't

It may be the primary key, but the system looked like it was throwing
away many rows based on type_id.  If it was throwing away many more rows
than found, the index with type_id may have been cheaper.

It is difficult to tell from an EXPLAIN ANALYZE as it doesn't tell you
exactly how many rows were filtered, just the cost to read them and how
many were used after the filter.

> help).  I also created a index on feature (type_id, feature_id), but the
> planner doesn't use it.  Also, there was an already existing index on
> feature (type_id) that the planner never used.

It cannot use more than one index for a given table scan at the moment.
There are proposals on how to 'fix' that, but those require significant
overhauls of various systems.

> Any other ideas?

Out of curiosity, what do you get if you disable hash joins?

set enable_hashjoin = false;


How about a partial index on (feature_id) where type_id = 219?


Вложения

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

От: Tom Lane
Дата:
Сообщение: Re: force the use of a particular index
От: "Nikolaus Dilger"
Дата:
Сообщение: Re: Dual Xeon + HW RAID question