Postgresql – How to optimize query based on explain analyze in PostgreSQL

explainpostgresql

I am running the following query via EXPLAIN ANALYZE but it is taking a bit of a long time to run. Based on the output of EXPLAIN ANALYZE, what are some things I can do to my table to make this query execute faster?

select count(amount), sum(amount) 
from mytable 
where color_code = 5 and shade_type = 'Light';

EXPLAIN ANALYZE output:

 Finalize Aggregate  (cost=946640.31..946640.32 rows=1 width=40) (actual time=4799.256..4799.257 rows=1 loops=1)
   ->  Gather  (cost=946640.09..946640.30 rows=2 width=40) (actual time=4799.191..4800.566 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=945640.09..945640.10 rows=1 width=40) (actual time=4797.002..4797.003 rows=1 loops=3)
               ->  Parallel Seq Scan on mytable  (cost=0.00..945573.39 rows=13338 width=6) (actual time=656.722..4791.404 rows=10103 loops=3)
                     Filter: ((color_code = 5) AND ((shade_type)::text = 'Light'::text))
                     Rows Removed by Filter: 4888180
 Planning time: 0.257 ms
 Execution time: 4800.661 ms
(10 rows)

Best Answer

You need this index to makenthe query fast:

CREATE INDEX ON mytable (color_code, shade_type);

If you want to aim for an index only scan, you can use

CREATE INDEX ON mytable (color_code, shade_type) INCLUDE (amount);

But that is only useful if the table gets vacuumed often enough, and you don't need HOT updates for the amount column.

Related Question