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)
