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!