Обсуждение: How to use brin_summarize_range
Good afternoon!
I am finding it difficult to understand how to maintain my BRIN index from the docs. Specifically, this is the documentation on the function brin_summarize_range which isn't clear to me:
brin_summarize_range(index regclass, blockNumber bigint) integer
- summarize the page range covering the given block, if not already summarized
There is no information on how a user is to actually find blockNumber, especially what blockNumber she might be interested in (like the end of the table). On my table, my BRIN index is all of a sudden all out of whack and I'm trying to figure out why. The planner doesn't choose it. Even if I force a BRIN scan, it estimates way wrong, and performs terribly. I do not have autosummarize on. I am curious if vacuum somehow invalidated everything?
When I ran brin_summarize_new_values, it immediately returned 0. This table has 292 million rows, and a straightforward insert-only pattern, but we also prune data older than 1 year old. The BRIN index is on insert time. It was working great up until just a bit ago.
Any direction on using these brin functions would be very appreciated.
Thanks,
Jeremy
On Thu, Aug 22, 2019 at 1:46 PM Jeremy Finzel <finzelj@gmail.com> wrote:
Good afternoon!I am finding it difficult to understand how to maintain my BRIN index from the docs. Specifically, this is the documentation on the function brin_summarize_range which isn't clear to me:brin_summarize_range(index regclass, blockNumber bigint) integer
- summarize the page range covering the given block, if not already summarized
I answered my own question (I think). blockNumber corresponds, I believe, to pages_per_range. So if I choose 64 as that value, I can run above function on 64 possible values. But perhaps I'm wrong about that?
There is no information on how a user is to actually find blockNumber, especially what blockNumber she might be interested in (like the end of the table). On my table, my BRIN index is all of a sudden all out of whack and I'm trying to figure out why. The planner doesn't choose it. Even if I force a BRIN scan, it estimates way wrong, and performs terribly. I do not have autosummarize on. I am curious if vacuum somehow invalidated everything?When I ran brin_summarize_new_values, it immediately returned 0. This table has 292 million rows, and a straightforward insert-only pattern, but we also prune data older than 1 year old. The BRIN index is on insert time. It was working great up until just a bit ago.Any direction on using these brin functions would be very appreciated.
I am also noticing bad plan choices with BRIN indexes on above scenario. I have tried creating said index with pages_per_range values of 64, 128, 500, 1000, and 10000. The 1000 value works best and executed in 11 seconds.
However, regardless of pages_per_change, the planner is still choosing a btree index on (id, id_2, insert_time) fields, which is taking 30 seconds to execute. I have to SET enable_indexscan TO false to get the BRIN index used, which is 3x faster. What gives?
Any suggestions?
Thanks,
Jeremy
Thanks,
Jeremy
What is default_statistics_target set to? Or is there custom statistics value set for this table/column? Perhaps the planner is making sub-optimal choices because it only has a vague idea about the data and the histogram is not very informative. Planning time will increase when statistics target is increased, but perhaps 250 instead of default 100 would give *enough* more information to make better choices. Or perhaps your target is already max 10000 and then I have no idea why btree would be chosen. Except, if correlation is too low for the optimizer to consider BRIN to be best perhaps. What does pg_stats say about the column in question?