PosgreSQL query slow when sorting on indexed timestamp colum I've the following issue: the table below:

    CREATE TABLE public.current_read
(
    id bigint NOT NULL DEFAULT nextval('current_read_id_seq'::regclass),
    cube_id bigint NOT NULL,
    end_time timestamp with time zone NOT NULL,
    period_type character varying COLLATE pg_catalog."default" NOT NULL,
    energy_balance double precision NOT NULL,
    created_at timestamp with time zone NOT NULL DEFAULT now(),
    CONSTRAINT current_read_pkey PRIMARY KEY (id),
    CONSTRAINT current_read_cube_id_fkey FOREIGN KEY (cube_id)
        REFERENCES cubemgmt.cube (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

-- Index: id-current_read-cube_id+end_time+period_type

-- DROP INDEX public."id-current_read-cube_id+end_time+period_type";

CREATE INDEX "id-current_read-cube_id+end_time+period_type"
    ON public.current_read USING btree
    (cube_id, end_time DESC, period_type COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: ix-current_read-cube_id+end_time

-- DROP INDEX public."ix-current_read-cube_id+end_time";

CREATE INDEX "ix-current_read-cube_id+end_time"
    ON public.current_read USING btree
    (cube_id, end_time DESC)
    TABLESPACE pg_default;

-- Index: ix-current_read-cube_id+period_type

-- DROP INDEX public."ix-current_read-cube_id+period_type";

CREATE INDEX "ix-current_read-cube_id+period_type"
    ON public.current_read USING btree
    (cube_id, period_type COLLATE pg_catalog."default")
    TABLESPACE pg_default;

-- Index: ix-currrent_read-end_time

-- DROP INDEX public."ix-currrent_read-end_time";

CREATE INDEX "ix-currrent_read-end_time"
    ON public.current_read USING btree
    (end_time DESC)
    TABLESPACE pg_default;

ALTER TABLE public.current_read
    CLUSTER ON "ix-currrent_read-end_time";


has currently around 60 million entries.

When I do a simple query:

SELECT energy_balance 
                  FROM public.current_read
                  WHERE cube_id = 5
                  AND period_type = '1min'
                  ORDER BY end_time desc 
                  LIMIT 120


it takes around 3-4 seconds to return the results.
If I remove the ORDER BY statement, I get (random) results in just a few miliseconds.

Explain plain looks like the following:

"Limit  (cost=730066.65..730080.65 rows=120 width=16) (actual time=1560.745..1613.023 rows=120 loops=1)"
"  -&gt;  Gather Merge  (cost=730066.65..772726.46 rows=365630 width=16) (actual time=1560.743..1613.000 rows=120 loops=1)"
"        Workers Planned: 2"
"        Workers Launched: 2"
"        -&gt;  Sort  (cost=729066.63..729523.67 rows=182815 width=16) (actual time=1538.981..1538.991 rows=101 loops=3)"
"              Sort Key: end_time DESC"
"              Sort Method: top-N heapsort  Memory: 30kB"
"              Worker 0:  Sort Method: top-N heapsort  Memory: 30kB"
"              Worker 1:  Sort Method: top-N heapsort  Memory: 30kB"
"              -&gt;  Parallel Bitmap Heap Scan on current_read  (cost=11257.81..721839.14 rows=182815 width=16) (actual time=432.012..1510.892 rows=138113 loops=3)"
"                    Recheck Cond: ((cube_id = 5) AND ((period_type)::text = '1min'::text))"
"                    Heap Blocks: exact=120165"
"                    -&gt;  Bitmap Index Scan on "ix-current_read-cube_id+period_type"  (cost=0.00..11148.13 rows=438756 width=0) (actual time=360.098..360.098 rows=414338 loops=1)"
"                          Index Cond: ((cube_id = 5) AND ((period_type)::text = '1min'::text))"
"Planning Time: 0.297 ms"
"Execution Time: 1613.112 ms"


So it looks like that query is not taking index "ix-current_read-end_time" into consideration.

Any idea how to make sure the index is used or how to improve the query timing?

Thanks a lot,

Cheers

Fritz
The index on (cube_id, period_type) cannot be used for the ORDER BY. But it can implement the AND period_type = '1min' very efficiently.

The index on (cube_id, end_time, period_type) can be used for the ORDER BY, but cannot be used efficiently for the AND period_type = '1min'.  It can be used, just not efficiently.  It can filter the rows in the index without visiting the table, but it can't jump to a specific part of the index where it knows all of the '1min' values are located.  (The reason is that all the '1min' values within a given cube_id are not located together)

So, it has to make a choice, efficient ORDER BY or efficient period_type = '1min'.  Perhaps it is making the wrong choice, although you haven't shown evidence of that.

You should create the index (cube_id, period_type, end_time).  Then it does not have to make a choice, it gets the best of both.  It can jump to the part of the index where cube_id = 5 AND period_type = '1min', and once there then it can read the rows in order by end_time.