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
arca...@gmail.com | @arcivanov | https://ivanov.biz
https://github.com/arcivanov
