Question: Postgresql JSON index strange query time

Question

Postgresql JSON index strange query time

Answers 0
Added at 2016-12-09 13:12
Tags
Question

Let's say we have table like this:

CREATE TABLE user_device_infos
(
  id integer NOT NULL DEFAULT nextval('user_device_infos_id_seq1'::regclass),
  user_id integer,
  data jsonb,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT user_device_infos_pkey PRIMARY KEY (id),
  CONSTRAINT fk_rails_e4001464ba FOREIGN KEY (user_id)
      REFERENCES public.users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

CREATE INDEX index_user_device_infos_imei_user_id
  ON public.user_device_infos
  USING btree
  (((data -> 'Network'::text) ->> 'IMEI No'::text) COLLATE pg_catalog."default", user_id);

CREATE INDEX index_user_device_infos_on_user_id
  ON public.user_device_infos
  USING btree
  (user_id);

Now i try to select user_id from first device with the same imei:

SELECT  user_id FROM user_device_infos WHERE (data->'Network'->>'IMEI No' = 'xxxx') order by id LIMIT 1

This query takes 5 seconds on my table ( 152000 entries ) But if i write

SELECT  user_id FROM user_device_infos WHERE (data->'Network'->>'IMEI No' = 'xxxx') order by created_at asc LIMIT 1
SELECT  user_id FROM user_device_infos WHERE (data->'Network'->>'IMEI No' = 'xxxx') order by created_at desc LIMIT 1

query takes less then 1 ms. Why this query i so much faster then first variant ? There are no indexes on created at, and id is primary key

Upd

As suggested in comments, i ran explain analyze, but still don't understand what is wrong with "order by id" query. Sorry, i am not a sql-dev

# explain analyze SELECT  user_id FROM user_device_infos WHERE (data->'Network'->>'IMEI No' = 'xxxx') order by id LIMIT 1;
                                                                         QUERY PLAN                                                                          
-------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.42..416.84 rows=1 width=8) (actual time=5289.784..5289.784 rows=0 loops=1)
   ->  Index Scan using user_device_infos_pkey on user_device_infos  (cost=0.42..316483.14 rows=760 width=8) (actual time=5289.782..5289.782 rows=0 loops=1)
         Filter: (((data -> 'Network'::text) ->> 'IMEI No'::text) = 'xxxx'::text)
         Rows Removed by Filter: 152437
 Planning time: 0.153 ms
 Execution time: 5289.817 ms
(6 rows)
# explain analyze SELECT  user_id FROM user_device_infos WHERE (data->'Network'->>'IMEI No' = 'xxxx') order by created_at LIMIT 1;
                                                                         QUERY PLAN                                                                         
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=2823.73..2823.74 rows=1 width=12) (actual time=0.064..0.064 rows=0 loops=1)
   ->  Sort  (cost=2823.73..2825.63 rows=760 width=12) (actual time=0.062..0.062 rows=0 loops=1)
         Sort Key: created_at
         Sort Method: quicksort  Memory: 25kB
         ->  Bitmap Heap Scan on user_device_infos  (cost=22.31..2819.93 rows=760 width=12) (actual time=0.039..0.039 rows=0 loops=1)
               Recheck Cond: (((data -> 'Network'::text) ->> 'IMEI No'::text) = 'xxxx'::text)
               ->  Bitmap Index Scan on index_user_device_infos_imei_user_id  (cost=0.00..22.12 rows=760 width=0) (actual time=0.037..0.037 rows=0 loops=1)
                     Index Cond: (((data -> 'Network'::text) ->> 'IMEI No'::text) = 'xxxx'::text)
 Planning time: 0.144 ms
 Execution time: 0.092 ms
(10 rows)
Answers
Source Show
◀ Wstecz