0

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)"
"  ->  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"
"        ->  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"
"              ->  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"
"                    ->  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

  • Can you try CREATE INDEX ON public.current_read (cube_id, period_type, end_time DESC) – Sahap Asci Oct 22 at 13:38
2

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.

  • thanks. One (stupid) question that I might not have been aware: as there is already an index: (cube_id, end_time DESC, period_type): is this one different from (cube_id, period_type, end_time DESC), so does the order of the columns in the index matter? So, changing the index (or creating an additional index with different columns) would solve the issue? Thanks a lot! – fritz Oct 23 at 13:22
  • For the default index type (btree), the column order matters a lot. Think of it like a paper phone book sorted by (lastname, firstname, address). It is easy to find all the "Johnson"s, not easy to find all the "Beverly"s. – jjanes Oct 23 at 13:54
  • Thanks! So clear but I did not put attention to this as it did not affect performance till now. Thanks a lot. – fritz Oct 25 at 6:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.