explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yxiv

Settings
# exclusive inclusive rows x rows loops node
1. 0.010 21,200.740 ↑ 1.0 100 1

Limit (cost=2,269,564.05..2,269,586.55 rows=100 width=87) (actual time=21,200.644..21,200.740 rows=100 loops=1)

2. 0.097 21,200.730 ↑ 1,331.9 100 1

GroupAggregate (cost=2,269,564.05..2,298,199.67 rows=133,189 width=87) (actual time=21,200.642..21,200.730 rows=100 loops=1)

  • Group Key: a."interval", a.marketareaid, a.tag
3. 0.238 21,200.633 ↑ 13,187.0 101 1

Sort (cost=2,269,564.05..2,272,893.77 rows=1,331,889 width=83) (actual time=21,200.628..21,200.633 rows=101 loops=1)

  • Sort Key: a."interval", a.tag
  • Sort Method: quicksort Memory: 60kB
4. 0.079 21,200.395 ↑ 5,243.7 254 1

Subquery Scan on a (cost=1,804,132.61..2,006,607.36 rows=1,331,889 width=83) (actual time=20,823.114..21,200.395 rows=254 loops=1)

5. 0.000 21,200.316 ↑ 5,243.7 254 1

Finalize GroupAggregate (cost=1,804,132.61..1,993,288.47 rows=1,331,889 width=99) (actual time=20,823.112..21,200.316 rows=254 loops=1)

  • Group Key: d."interval", c.marketareaid, a_1.businessid, b.tag
6. 367.634 21,531.351 ↑ 1,456.6 762 1

Gather Merge (cost=1,804,132.61..1,951,666.93 rows=1,109,908 width=99) (actual time=20,821.706..21,531.351 rows=762 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
7. 147.325 21,163.717 ↑ 2,184.9 254 3 / 3

Partial GroupAggregate (cost=1,803,132.59..1,822,555.98 rows=554,954 width=99) (actual time=20,808.565..21,163.717 rows=254 loops=3)

  • Group Key: d."interval", c.marketareaid, a_1.businessid, b.tag
8. 1,182.719 21,016.392 ↑ 1.1 512,855 3 / 3

Sort (cost=1,803,132.59..1,804,519.98 rows=554,954 width=75) (actual time=20,800.999..21,016.392 rows=512,855 loops=3)

  • Sort Key: d."interval", a_1.businessid, b.tag
  • Sort Method: external merge Disk: 54,272kB
  • Worker 0: Sort Method: external merge Disk: 51,840kB
  • Worker 1: Sort Method: external merge Disk: 51,384kB
9. 1,501.253 19,833.673 ↑ 1.1 512,855 3 / 3

Parallel Hash Join (cost=1,221,604.45..1,725,523.41 rows=554,954 width=75) (actual time=19,315.538..19,833.673 rows=512,855 loops=3)

  • Hash Cond: ((a_1.itemid)::text = (b.id)::text)
10. 57.762 594.177 ↓ 3.2 229,816 3 / 3

Nested Loop (cost=9.75..455,788.89 rows=71,685 width=100) (actual time=271.440..594.177 rows=229,816 loops=3)

  • Join Filter: ((c.id)::text = (a_1.businessid)::text)
11. 0.188 269.474 ↓ 1.4 31 3 / 3

Nested Loop (cost=9.06..118,075.29 rows=22 width=95) (actual time=269.329..269.474 rows=31 loops=3)

12. 263.773 263.773 ↑ 1.0 12 3 / 3

Parallel Seq Scan on businessmarketareaids c (cost=0.00..114,430.08 rows=12 width=50) (actual time=17.827..263.773 rows=12 loops=3)

  • Filter: (((markettype)::text = 'projectionCell'::text) AND ((marketareaid)::text = 'projectionCell^0.0.0.0.4.2'::text))
  • Rows Removed by Filter: 1,904,973
13. 0.173 5.513 ↑ 27.0 3 37 / 3

Bitmap Heap Scan on businessorderaggs d (cost=9.06..302.96 rows=81 width=45) (actual time=0.439..0.447 rows=3 loops=37)

  • Recheck Cond: ((id)::text = (c.id)::text)
14. 5.340 5.340 ↑ 27.0 3 37 / 3

Bitmap Index Scan on businessorderaggs_pkey (cost=0.00..9.04 rows=81 width=0) (actual time=0.433..0.433 rows=3 loops=37)

  • Index Cond: ((id)::text = (c.id)::text)
15. 266.941 266.941 ↓ 1.6 7,413 93 / 3

Index Scan using dailybusinessitemagg_pkey on dailybusinessitemagg a_1 (cost=0.70..15,292.27 rows=4,668 width=60) (actual time=0.493..8.611 rows=7,413 loops=93)

  • Index Cond: (((businessid)::text = (d.id)::text) AND (day >= d.earliest) AND (day <= d.latest))
16. 2,378.028 17,738.243 ↑ 1.2 4,326,984 3 / 3

Parallel Hash (cost=1,112,699.45..1,112,699.45 rows=5,360,980 width=33) (actual time=17,738.243..17,738.243 rows=4,326,984 loops=3)

  • Buckets: 65,536 Batches: 512 Memory Usage: 2,336kB
17. 15,360.215 15,360.215 ↑ 1.2 4,326,984 3 / 3

Parallel Seq Scan on itemtags b (cost=0.00..1,112,699.45 rows=5,360,980 width=33) (actual time=0.029..15,360.215 rows=4,326,984 loops=3)

  • Filter: ((tagtype)::text = ANY ('{M,CH,PT}'::text[]))