Re: How to use brin_summarize_range

On Thu, Aug 22, 2019 at 1:46 PM Jeremy Finzel <finzelj(at)gmail(dot)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