Re: PostgreSQL does not choose my indexes well

Поиск
Список
Период
Сортировка
От Tom Lane
Тема Re: PostgreSQL does not choose my indexes well
Дата
Msg-id 18681.1587664601@sss.pgh.pa.us
обсуждение исходный текст
Ответ на Re: PostgreSQL does not choose my indexes well  (Stephen Frost <sfrost@snowman.net>)
Ответы Re: PostgreSQL does not choose my indexes well
Список pgsql-performance
Stephen Frost <sfrost@snowman.net> writes:
> I do wonder if we are maybe missing a bet at times though, considering
> that I'm pretty sure we'll always go through the index in order, and
> therefore randomly, even when we don't actually need the results in
> order..?  Has there been much consideration for just opening an index
> and sequentially scanning it in cases like this where we have to go
> through all of the index anyway and don't need the results in order?

As I recall, it's unsafe to do so because of consistency considerations,
specifically there's a risk of missing or double-visiting some entries due
to concurrent index page splits.  VACUUM has some way around that, but it
doesn't work for regular data-fetching cases.  (nbtree/README has more
about this, but I don't feel like looking it up for you.)

My guess based on your results is that the OP's table *isn't* all-visible,
or at least the planner doesn't know it is.

            regards, tom lane



В списке pgsql-performance по дате отправления:

Предыдущее
От: Stephen Frost
Дата:
Сообщение: Re: PostgreSQL does not choose my indexes well
Следующее
От: Stephen Frost
Дата:
Сообщение: Re: PostgreSQL does not choose my indexes well