postgresql – Extreme slow query for a simple indexed selection – Why?


i have a table in my database that is described as follows:

-- Table: public.devicemeasurement_mqtt

-- DROP TABLE public.devicemeasurement_mqtt;

CREATE TABLE public.devicemeasurement_mqtt
(
    measurement_id integer NOT NULL DEFAULT nextval('devicemeasurement_mqtt_measurement_id_seq'::regclass),
    insert_time timestamp with time zone NOT NULL,
    data jsonb NOT NULL,
    smart_device_id integer NOT NULL,
    CONSTRAINT devicemeasur_smart_device_id_ee500e1e_fk_d FOREIGN KEY (smart_device_id)
        REFERENCES public.device_mqtt (device_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        DEFERRABLE INITIALLY DEFERRED
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.devicemeasurement_mqtt
    OWNER to postgres;

GRANT ALL ON TABLE public.devicemeasurement_mqtt TO postgres;

-- Index: dev_insert__2df020_idx

-- DROP INDEX public.dev_insert__2df020_idx;

CREATE INDEX dev_insert__2df020_idx
    ON public.devicemeasurement_mqtt USING btree
    (insert_time DESC NULLS FIRST)
    TABLESPACE pg_default;


-- Index: devicemeasurement_mqtt_smart_device_id_ee500e1e

-- DROP INDEX public.devicemeasurement_mqtt_smart_device_id_ee500e1e;

CREATE INDEX devicemeasurement_mqtt_smart_device_id_ee500e1e
    ON public.devicemeasurement_mqtt USING btree
    (smart_device_id ASC NULLS LAST)
    TABLESPACE pg_default;

This table, is continuously used from a service that inserts tons of data every minute (at this time it contains around 3 milions of rows), now, in the current situation, when i have to select one device that has continuous measurements the problem does not exist, but, suppose that i have to select the measurements for a specific device that was in use three months ago and now it’s disabled (so, no more new measurements), the execution of a simple and effective query like below is really slow, taking up 40 seconds to complete:

This is the reference query that generates this slow select:

EXPLAIN (BUFFERS,ANALYZE) SELECT "devicemeasurement_mqtt"."measurement_id", "devicemeasurement_mqtt"."insert_time", "devicemeasurement_mqtt"."data", "devicemeasurement_mqtt"."smart_device_id" FROM "devicemeasurement_mqtt" WHERE "devicemeasurement_mqtt"."smart_device_id" = 4 ORDER BY "devicemeasurement_mqtt"."insert_time" DESC  LIMIT 15

and here you can find: the explain and the database configuration

I’ll be glad if someone will help me to figure out!