From: | Michael Lewis <mlewis(at)entrata(dot)com> |
---|---|
To: | Peter Adlersburg <peter(dot)adlersburg(at)gmail(dot)com> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: Advice needed: query performance deteriorates by 2000% within 1 minute |
Date: | 2022-02-24 15:05:50 |
Message-ID: | CAHOFxGoebz59tiFJCuKYxP6S60L04jP3TUQ8c-1x5ChVjVV7gQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-02-24 16:10:51 | Re: Advice needed: query performance deteriorates by 2000% within 1 minute |
Previous Message | Peter Adlersburg | 2022-02-24 13:53:12 | Advice needed: query performance deteriorates by 2000% within 1 minute |