Since the optimizer is choosing a seq scan over index scan when it seems like it has good row estimates in both cases, to me that may mean costs of scanning index are expected to be high. Is this workload on SSD? Has the random_page_cost config been decreased from default 4 (compared with cost of 1 unit for sequential scan)?
It's 1.5
Your buffer hits aren't great. What is shared_buffers set to? How much ram on this cluster?
shared_buffers is 4GB. It has 500G of RAM, but server has several clusters on it.
With this table being insert only, one assumes correlation is very high on the data in this column as shown in pg_stats, but have your confirmed?
Yes, but the issue isn't with the BRIN index performing badly or being fragmented. It's that it performs great (7x faster than the seq scan) but postgres doesn't pick using it. I have seen this same issue also in other attempts I have made to use BRIN.
To me, distinct ON is often a bad code smell and probably can be re-written to be much more efficient with GROUP BY, lateral & order by, or some other tool. Same with the window function. It is a powerful tool, but sometimes not the right one.
I don't really agree, but it's beside the point because the issue is not in aggregation. It's pre-aggregation. Indeed if I run my query as a simple select (as I tried) it's the exact same planning issue. (In my experience, distinct on for given example is the fastest. Same with window functions which prevent inefficient self-joins)
Is "source" a function that is called on field1? What is it doing/how is it defined?
I can't see how that matters either, but the "source" function is a mask for a built-in pg function that is trivial. This whole query is masked so as not to expose our actual prod query, but I hope it's still understandable enough :).
My question is not how to make this query faster in general. It's that I want to use BRIN indexes very much, but I'm not sure I can trust they will scale with the right query plan like I know BTREE will.
Thanks!
Jeremy