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';


 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)
  • 1
    Is there an index on color_code,shade_type? – danblack May 21 '19 at 3:22

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.

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.