4

Is it possible to create effective index on jsonb column to timestamp value in postgresql?

I tried this advice Create timestamp index from JSON on PostgreSQL but ORDER and SIMPLE WHERE operations are still using seq scan without index.

And query execution time with an index and without index no difference:

SELECT * FROM bums.mytable_no_ind ORDER BY (ts->>'date')::TIMESTAMP DESC

and

EXPLAIN ANALYZE SELECT * FROM bums.mytable_no_ind WHERE (ts->>'date')::TIMESTAMP = NOW()

Information about table:

    create table bums.mytable_no_ind (
    s serial primary key,
    ts jsonb,
    x int,
    y int,
    z int
);
insert into bums.mytable_no_ind (ts, x, y, z) 
select 
    ('{"date": "' || clock_timestamp() || '"}')::jsonb,
    500 * RANDOM(),
    500 * RANDOM(),
    500 * RANDOM()
from generate_series(1, 1000000)
;
Community
  • 1
  • 1
Игорь Наразин
  • 47
  • 5
  • http://wiki.postgresql.org/wiki/SlowQueryQuestions –  May 19 '16 at 14:53
  • a_horse_with_no_name, my question is not about slow query. I asked about possible timestamp index in jsonb columns – Игорь Наразин May 19 '16 at 14:56
  • Well the answer you linked to clearly shows a way to create such an index. The part "*query execution time no difference*" indicates that you _are_ concerned about the performance. What exactly **is** your question then? –  May 19 '16 at 14:57
  • You should read carefully the linked answer, because it states clearly why the cast-ing to `timestamp` cannot be indexed. – pozs May 19 '16 at 15:04
  • I tried to create index from answer in linked question. But this index not usage in query. In query plain seq scan still usage. But I expected my index usage. And i asked - why index is not used in the queries in my post? – Игорь Наразин May 19 '16 at 15:06
  • I found the problem. I just change (ts->>'date')::TIMESTAMP to f_cast_isots(ts->>'date') and index is work! – Игорь Наразин May 19 '16 at 15:24

0 Answers0