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
