Re: Advice needed: query performance deteriorates by 2000% within 1 minute

Поиск
Список
Период
Сортировка
От Michael Lewis
Тема Re: Advice needed: query performance deteriorates by 2000% within 1 minute
Дата
Msg-id CAHOFxGoebz59tiFJCuKYxP6S60L04jP3TUQ8c-1x5ChVjVV7gQ@mail.gmail.com
обсуждение исходный текст
Ответ на Advice needed: query performance deteriorates by 2000% within 1 minute  (Peter Adlersburg <peter.adlersburg@gmail.com>)
Список pgsql-performance
You are getting row estimate 48 in both cases, so it seems perhaps tied to the free space map that will mean more heap lookups from the index, to the point where the planner thinks that doing sequential scan is less costly.

What is random_page_cost set to? Do you have default autovacuum/analyze settings?

It is probably worth running "explain (analyze, buffers, verbose) select..." to get a bit more insight. I expect that the buffers increase gradually and then it switches to sequential scan at some point.


Perhaps not directly related, but might be interesting to look at-
With indexes on expressions, you get custom stats. It might be worth taking a look at those and seeing if they give anything approaching proper estimates.

eg.
select * from pg_class where relname = 'idx_customer_phone_numbers_phone_number_gist';
select * from pg_statistic where starelid = 'idx_customer_phone_numbers_phone_number_gist'::regclass;
select * from pg_stats where tablename = 'idx_customer_phone_numbers_phone_number_gist';

JSONB is a bit painful to use from a query planning perspective. Values in a jsonb column are fine for me in a select clause, but not ON or WHERE with very rare exceptions. Though, maybe that's not so applicable when you are doing full text search.

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

Предыдущее
От: Peter Adlersburg
Дата:
Сообщение: Advice needed: query performance deteriorates by 2000% within 1 minute
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Advice needed: query performance deteriorates by 2000% within 1 minute