Optimizer misses big in 10.4 with BRIN index

Hi,

Before spamming the list with reproduction examples I want to make sure
the issue isn't already known.

Moving to 10.4 from 9.2 (AWS RDS but repro on local laptop as well)
we've discovered that an optimizer prefers a seq scan to fully analyzed
consistent BRIN index, ending up with a query that is 4.8s long on
seqscan vs 56ms when forcing use of BRIN (85 times difference).
The size of the dataset is millions of rows and with extremely high
probability the rows are naturally clustered on BRIN index column.

Anybody observed anything like that?

========================================================
schema0=# SET enable_seqscan=false;
SET
schema0=# EXPLAIN (analyze, verbose, costs, buffers) SELECT data,
count(*) OVER() AS full_count FROM schema0_lab.data_table WHERE segment
= 'pb1'
                    AND (data->>'tradeDate')::numeric >= '1531267200'
                    AND (data->>'tradeDate')::numeric <= '1531353600'
                    AND data->>'tradeStatus' = 'Replaced'
                    ORDER BY (data->>'tradeDate')::numeric
                    DESC;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=617851.03..617851.24 rows=84 width=1219) (actual
time=55.765..55.794 rows=611 loops=1)
   Output: data, (count(*) OVER (?)), (((data ->>
'tradeDate'::text))::numeric)
   Sort Key: (((data_table.data ->> 'tradeDate'::text))::numeric) DESC
   Sort Method: quicksort  Memory: 1256kB
   Buffers: shared hit=824
   ->  WindowAgg  (cost=1231.98..617848.34 rows=84 width=1219) (actual
time=52.688..55.068 rows=611 loops=1)
         Output: data, count(*) OVER (?), ((data ->>
'tradeDate'::text))::numeric
         Buffers: shared hit=824
         ->  Gather  (cost=1231.98..617846.66 rows=84 width=1179)
(actual time=8.247..51.804 rows=611 loops=1)
               Output: data
               Workers Planned: 2
               Workers Launched: 2
               Buffers: shared hit=824
               ->  Parallel Bitmap Heap Scan on schema0_lab.data_table 
(cost=231.98..616838.26 rows=35 width=1179) (actual time=3.850..46.704
rows=204 loops=3)
                     Output: data
                     Recheck Cond: ((((data_table.data ->>
'tradeDate'::text))::numeric >= '1531267200'::numeric) AND
(((data_table.data ->> 'tradeDate'::text))::numeric <=
'1531353600'::numeric))
                     Rows Removed by Index Recheck: 4404
                     Filter: (((data_table.segment)::text =
'pb1'::text) AND ((data_table.data ->> 'tradeStatus'::text) =
'Replaced'::text))
                     Heap Blocks: lossy=794
                     Buffers: shared hit=2334
                     Worker 0: actual time=3.572..44.145 rows=236 loops=1
                       Buffers: shared hit=749
                     Worker 1: actual time=0.326..45.184 rows=212 loops=1
                       Buffers: shared hit=761
                     ->  Bitmap Index Scan on tradedate_idx
(cost=0.00..231.96 rows=3377106 width=0) (actual time=4.500..4.500
rows=23040 loops=1)
                           Index Cond: ((((data_table.data ->>
'tradeDate'::text))::numeric >= '1531267200'::numeric) AND
(((data_table.data ->> 'tradeDate'::text))::numeric <=
'1531353600'::numeric))
                           Buffers: shared hit=30
 Planning time: 0.246 ms
 Execution time: 56.209 ms
(29 rows)

schema0=# SET enable_seqscan=true;
SET
schema0=# EXPLAIN (analyze, verbose, costs, buffers) SELECT data,
count(*) OVER() AS full_count FROM schema0_lab.data_table WHERE segment
= 'pb1'
                    AND (data->>'tradeDate')::numeric >= '1531267200'
                    AND (data->>'tradeDate')::numeric <= '1531353600'
                    AND data->>'tradeStatus' = 'Replaced'
                    ORDER BY (data->>'tradeDate')::numeric
                    DESC;
QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------
 Sort  (cost=617619.05..617619.26 rows=84 width=1219) (actual
time=4823.081..4823.106 rows=611 loops=1)
   Output: data, (count(*) OVER (?)), (((data ->>
'tradeDate'::text))::numeric)
   Sort Key: (((data_table.data ->> 'tradeDate'::text))::numeric) DESC
   Sort Method: quicksort  Memory: 1256kB
   Buffers: shared hit=839 read=187353
   ->  WindowAgg  (cost=1000.00..617616.36 rows=84 width=1219) (actual
time=4820.005..4822.390 rows=611 loops=1)
         Output: data, count(*) OVER (?), ((data ->>
'tradeDate'::text))::numeric
         Buffers: shared hit=839 read=187353
         ->  Gather  (cost=1000.00..617614.68 rows=84 width=1179)
(actual time=3.262..4819.362 rows=611 loops=1)
               Output: data
               Workers Planned: 2
               Workers Launched: 2
               Buffers: shared hit=839 read=187353
               ->  Parallel Seq Scan on schema0_lab.data_table
(cost=0.00..616606.28 rows=35 width=1179) (actual time=6.135..4814.826
rows=204 loops=3)
                     Output: data
                     Filter: (((data_table.segment)::text =
'pb1'::text) AND ((data_table.data ->> 'tradeStatus'::text) =
'Replaced'::text) AND (((data_table.data ->>
'tradeDate'::text))::numeric >= '1531267200'::numeric) AND
(((data_table.data
 ->> 'tradeDate'::text))::numeric <= '1531353600'::numeric))
                     Rows Removed by Filter: 1125498
                     Buffers: shared hit=2400 read=561439
                     Worker 0: actual time=11.414..4812.744 rows=220
loops=1
                       Buffers: shared hit=775 read=187333
                     Worker 1: actual time=4.249..4813.264 rows=220 loops=1
                       Buffers: shared hit=786 read=186753
 Planning time: 0.232 ms
 Execution time: 4823.412 ms
(24 rows)

schema0=# \d schema0_lab.data_table

                                         Table "schema0_lab.data_table"
   Column   |          Type          | Collation | Nullable
|                     Default
------------+------------------------+-----------+----------+--------------------------------------------------
 id         | integer                |           | not null |
nextval('schema0_lab.data_table_id_seq'::regclass)
 address    | character varying(128) |           | not null |
 segment    | character varying(128) |           | not null |
 data       | jsonb                  |           | not null |
Indexes:
    "data_table_pkey" PRIMARY KEY, btree (id)
    "tradedate_idx" brin (((data ->> 'tradeDate'::text)::numeric)) WITH
(autosummarize='true')

--
Arcadiy Ivanov
arcadiy(at)gmail(dot)com | @arcivanov | https://ivanov.biz
https://github.com/arcivanov