explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cXOZ

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 115,711.195 ↑ 17.3 6 1

Unique (cost=4,595,610.05..4,595,662.66 rows=104 width=40) (actual time=115,711.179..115,711.195 rows=6 loops=1)

2. 0.000 115,711.190 ↑ 17.3 6 1

Group (cost=4,595,610.05..4,595,661.88 rows=104 width=40) (actual time=115,711.176..115,711.190 rows=6 loops=1)

  • Group Key: ps.rank, pr.planogram_status, (get_translation_immutable(ps.label, 'en_US'::character varying))
3. 55.573 115,756.623 ↑ 11.6 18 1

Gather Merge (cost=4,595,610.05..4,595,634.32 rows=208 width=40) (actual time=115,711.171..115,756.623 rows=18 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
4. 0.075 115,701.050 ↑ 17.3 6 3 / 3

Sort (cost=4,594,960.02..4,594,960.28 rows=104 width=40) (actual time=115,701.049..115,701.050 rows=6 loops=3)

  • Sort Key: ps.rank, pr.planogram_status, (get_translation_immutable(ps.label, 'en_US'::character varying))
  • Sort Method: quicksort Memory: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 25kB
5. 4,341.567 115,700.975 ↑ 17.3 6 3 / 3

Partial HashAggregate (cost=4,594,929.50..4,594,956.54 rows=104 width=40) (actual time=115,700.974..115,700.975 rows=6 loops=3)

  • Group Key: ps.rank, pr.planogram_status, get_translation_immutable(ps.label, 'en_US'::character varying)
6. 88,035.526 111,359.408 ↑ 1.2 7,548,153 3 / 3

Hash Left Join (cost=1,108,926.17..4,524,742.01 rows=9,358,332 width=40) (actual time=13,186.901..111,359.408 rows=7,548,153 loops=3)

  • Hash Cond: (pr.planogram_status = ps.planogram_status)
7. 5,952.237 23,323.825 ↑ 1.2 7,548,153 3 / 3

Parallel Hash Join (cost=1,108,924.88..2,152,583.52 rows=9,358,332 width=4) (actual time=13,185.343..23,323.825 rows=7,548,153 loops=3)

  • Hash Cond: (pr.planogram = p.planogram)
8. 4,196.457 4,196.457 ↑ 1.2 7,548,153 3 / 3

Parallel Seq Scan on tb_planogram_revision pr (cost=0.00..957,873.94 rows=9,358,332 width=8) (actual time=0.022..4,196.457 rows=7,548,153 loops=3)

  • Filter: (planogram_status <> ALL ('{4,6,7,11,12}'::integer[]))
  • Rows Removed by Filter: 4,412,292
9. 471.683 13,175.131 ↑ 1.2 1,276,993 3 / 3

Parallel Hash (cost=1,088,975.85..1,088,975.85 rows=1,595,922 width=4) (actual time=13,175.131..13,175.131 rows=1,276,993 loops=3)

  • Buckets: 4,194,304 Batches: 1 Memory Usage: 182,688kB
10. 2,247.191 12,703.448 ↑ 1.2 1,276,993 3 / 3

Merge Left Join (cost=0.99..1,088,975.85 rows=1,595,922 width=4) (actual time=0.063..12,703.448 rows=1,276,993 loops=3)

  • Merge Cond: (p.dv_latest_planogram_revision = ckpr.replaces)
11. 1,225.234 1,225.234 ↑ 1.2 1,276,993 3 / 3

Parallel Index Scan using ix_planogram_dv_latest_planogram_revision on tb_planogram p (cost=0.43..138,104.50 rows=1,595,922 width=8) (actual time=0.033..1,225.234 rows=1,276,993 loops=3)

  • Filter: ((NOT obsolete) AND (NOT is_empty))
12. 9,231.023 9,231.023 ↑ 1.6 22,405,220 3 / 3

Index Scan using ix_planogram_revision_planogram_revision_replaces on tb_planogram_revision ckpr (cost=0.56..1,394,150.77 rows=35,878,588 width=12) (actual time=0.022..9,231.023 rows=22,405,220 loops=3)

13. 0.021 0.057 ↑ 1.0 13 3 / 3

Hash (cost=1.13..1.13 rows=13 width=42) (actual time=0.057..0.057 rows=13 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
14. 0.036 0.036 ↑ 1.0 13 3 / 3

Seq Scan on tb_planogram_status ps (cost=0.00..1.13 rows=13 width=42) (actual time=0.034..0.036 rows=13 loops=3)

Planning time : 1.470 ms
Execution time : 115,756.879 ms