postgresqlindexingsql-tuningpostgresql-json

Index scan is not working on json data set in postgres

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?

Solution

  • 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))