Oh, I misplaced the added where conditions.
It should have been as follows, however, the overall idea is the same
--- orignial.sql
+++ tuned_v2.sql
@@ -83,6 +83,7 @@
AND (judg1.jrt_opt_out_flag <> 'Y' OR
judg1.jrt_opt_out_flag IS NULL)
) sub0
Steve,
It looks like PostgreSQL was not able to push the join condition into group
by subquery.
The problematic bits in the PostgreSQL plan are
-> Nested Loop Left Join (cost=3423317.32..4522882.01 rows=4
width=261) (actual time=30839.132..35117.682 rows=39 loops=1)
> You could for example (when looking for a MAX) skip scanning
block ranges whose indexed MAX is less than the indexed MIN of some
other block range.
When looking for a max, you could scan the range with the maximal indexed
MAX, and then you could skip all the ranges that have MAX less than the
> so you can't deduce in which range the current min or max value is from
there.
That is why you select several candidate ranges and scan the table for
those ranges.
For instance, if you have ranges
1) 1..4
2) 5..8
3) 6..9
Then you do something like
select x
from (
select max(col) x from
Right you are I should have divided it by ranges per page.
Vladimir
--
Vladimir
> Is it correct that BRIN indexes don't support MIN/MAX operations ?
In theory, it should be possible to implement min/max scan support for
BRIN, however it is not implemented yet.
Just in case, min/max query would require to read all BRIN pages, and then
it would require to read the
Here's an interesting read on regex improvements in dot net 7
See "Goodbye, Boyer-Moore" where they drop Boyer-Moore and replace it with
vectorized search:
https://devblogs.microsoft.com/dotnet/regular-expression-improvements-in-dotnet-7/#vectorization
Vladimir
--
Vladimir