Index scan is not working on json data set in postgres

Question

I'm trying to understanding of extracting data from JSON files in PostgreSQL with certain filter conditions.

Here is my query,

created index like below,

CREATE INDEX idx_startTimeL_n
    ON mytable USING btree
    (((data -> 'info'::text) ->> 'startTimeL'::text) )

If I run the explain select query

EXPLAIN   SELECT * FROM mytable
      WHERE (((data -> 'info'::text) ->> 'startTimeL'::text)::double precision) <= (date_part('epoch'::text, now()) * 1000::double precision)
      AND ((data -> 'info'::text) ->> 'startTimeL'::text)::double precision) >= (date_part('epoch'::text, now()) * 1000::double precision - 3600000::double precision)
     LIMIT 400000;

Query planner results are,

"Limit  (cost=0.00..36371.90 rows=220700 width=1568)"
"  ->  Seq Scan on mytable  (cost=0.00..36371.90 rows=220700 width=1568)"
"        Filter: (((((data -> 'info'::text) ->> 'startTimeL'::text))::double precision <= (date_part('epoch'::text, now()) * '1000'::double precision)) AND ((((data -> 'info'::text) ->> 'startTimeL'::text))::double precision >= ((date_part('epoch'::text, now()) * '1000'::double precision) - '3600000'::double precision)))"

So, here my question is why the seq scan happening instead of index scan even though table indexed with filter condition?

Because your index expression is different to the expression used in the WHERE clause — , Apr 06 '20 at 18:35
Is it possible to create an index with the same given expression in where? — Rj_N, Apr 06 '20 at 18:39

score 1 · Accepted Answer · answered Apr 06 '20 at 22:19

Your example query has a syntax error, unbalanced paranetheses.

If you want to look up into an index with double precision, then that is how the index must be defined.

CREATE INDEX ON mytable USING btree
    (((data -> 'info' ->> 'startTimeL')::double precision))

Index scan is not working on json data set in postgres

1 Answers1