Re: Optimizer misses big in 10.4 with BRIN index

Hi,

On 07/25/2018 03:58 PM, Arcadiy Ivanov wrote:
>                      ->  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))

My guess is this is the root cause - the estimated number of rows is
much higher than in practice (3377106 vs. 23040), so at the end the
seqscan is considered to be slightly cheaper and wins. But the actual
row count is ~150x lower, making the bitmap index scan way faster.

IMHO you'll need to find a way to improve the estimates, which may be
difficult. The first thing I'd try is creating an expression index on
the expression you use in the WHERE clause. Something like

CREATE INDEX ON data_table (((data_table.data ->>
'tradeDate'::text))::numeric);

And then ANALYZE the table again ...

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services