Re: [PERFORM] Querying with multicolumn index

От: Tom Lane
Тема: Re: [PERFORM] Querying with multicolumn index
Дата: ,
Msg-id: 14793.1481417493@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: [PERFORM] Querying with multicolumn index  (Tomas Vondra)
Список: pgsql-performance

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

[PERFORM] Querying with multicolumn index  (Eric Jiang, )
 Re: [PERFORM] Querying with multicolumn index  (Andreas Joseph Krogh, )
  Re: [PERFORM] Querying with multicolumn index  (Eric Jiang, )
 Re: [PERFORM] Querying with multicolumn index  (Tom Lane, )
  Re: [PERFORM] Querying with multicolumn index  (Tomas Vondra, )
   Re: [PERFORM] Querying with multicolumn index  (Tom Lane, )
   Re: [PERFORM] Querying with multicolumn index  (Daniel Blanch Bataller, )
    Re: [PERFORM] Querying with multicolumn index  (Daniel Blanch Bataller, )
 Re: [PERFORM] Querying with multicolumn index  (Daniel Blanch Bataller, )
  Re: [PERFORM] Querying with multicolumn index  (Eric Jiang, )
   Re: [PERFORM] Querying with multicolumn index  (Tom Lane, )
    Re: [PERFORM] Querying with multicolumn index  (Eric Jiang, )

Tomas Vondra <> writes:
> On 12/10/2016 12:51 AM, Tom Lane wrote:
>> I tried to duplicate this behavior, without success.  Are you running
>> with nondefault planner parameters?

> My guess is this is a case of LIMIT the matching rows are uniformly
> distributed in the input data. The planner likely concludes that for a
> driver with a lot of data we'll find the first row using ix_updates_time
> very quickly, and that it will be cheaper than inspecting the larger
> multi-column index. But imagine a driver with a lots of data long time
> ago. That breaks the LIMIT fairly quickly.

The fact that it's slow enough to be a problem is doubtless related to
that effect.  But AFAICS, the planner should never prefer that index
for this query, because even with a uniform-density assumption, the
index that really matches the query ought to look better.

            regards, tom lane



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

От: Daniel Blanch Bataller
Дата:
Сообщение: Re: [PERFORM] Querying with multicolumn index
От: ProPAAS DBA
Дата:
Сообщение: [PERFORM] Isolation of tx logs on VMware