PostgreSQL improve a query-CodePudding

I have a query as follows:

SELECT MAX(c."Sequence") 
FROM "Cips" AS c 
WHERE c."StoreNumber" IN (1, 2) 
AND c."DataProvider" in ('MCIP'  , 'SAM')

I also run the explain analyse and I receive the following answer:

"Aggregate  (cost=43628.91..43628.92 rows=1 width=8) (actual time=81.290..81.292 rows=1 loops=1)"
"  ->  Append  (cost=0.43..43498.29 rows=52248 width=8) (actual time=0.090..75.045 rows=61163 loops=1)"
"        ->  Index Scan using ""a_StoreNumber_DataProvider_idx"" on a c  (cost=0.43..43237.05 rows=52248 width=8) (actual time=0.089..67.541 rows=61163 loops=1)"
"              Index Cond: ((""StoreNumber"" = ANY ('{-1,1}'::integer[])) AND (""DataProvider"" = ANY ('{MCIP,SAM}'::text[])))"
"Planning Time: 0.677 ms"
"Execution Time: 81.366 ms"

I have only one index defined on this table:

CREATE INDEX "idx_Cip_StoreNumber_Sequence_DataProvider"
    ON public."Cips" USING btree
    ("StoreNumber" ASC NULLS LAST, "Sequence" ASC NULLS LAST, "DataProvider" COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

My table row count is somewhere around 1.4 mil rows. I also tried to partition the table, but with 10% better performance. My problem is that I need to improve the performance by 90% and I really am stuck.

I was wondering if I can improve the performance of this query or should I start to look in some other direction, for example to modify the architecture around this field and how to we get the max value?

CodePudding user response：

Try a different index, where you have the columns ordered in the way you need them for this query:

CREATE INDEX "idx_Cip_StoreNumber_DataProvider_Sequence"
    ON public."Cips" USING btree
    ("StoreNumber" ASC NULLS LAST, "DataProvider" COLLATE pg_catalog."default" ASC NULLS LAST, "Sequence" DESC NULLS LAST, )
    TABLESPACE pg_default;

Sequence is now the last column in the index, and also sorted descending but NULLS last.

Does this improve the query plan? Please use EXPLAIN(ANALYZE, VERBOSE, BUFFERS) to get the complete plan.