explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VoA2

Settings
# exclusive inclusive rows x rows loops node
1. 161.027 30,956.944 ↑ 320.3 15 1

GroupAggregate (cost=138,368.73..138,861.17 rows=4,804 width=52) (actual time=30,665.913..30,956.944 rows=15 loops=1)

  • Group Key: o.company_id, o.state, (max((orec.auto_reconciliation_status)::text)), (CASE WHEN (((max(p.paid_out_at)) >= '2020-06-01 04:00:00'::timestamp without time zone) AND ((max(p.paid_out_at)) < '2020-07-01 03:59:59'::timestamp without time zone)) THEN true ELSE false END)
  • Filter: (sum(abs(o.tax)) > '0'::double precision)
  • Rows Removed by Filter: 126
  • JIT:
  • Functions: 76
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 10.331 ms, Inlining 0.000 ms, Optimization 3.591 ms, Emission 61.295 ms, Total 75.217 ms
2. 332.964 30,795.917 ↓ 1.5 20,912 1

Sort (cost=138,368.73..138,404.76 rows=14,413 width=52) (actual time=30,646.239..30,795.917 rows=20,912 loops=1)

  • Sort Key: o.state, (max((orec.auto_reconciliation_status)::text)), (CASE WHEN (((max(p.paid_out_at)) >= '2020-06-01 04:00:00'::timestamp without time zone) AND ((max(p.paid_out_at)) < '2020-07-01 03:59:59'::timestamp without time zone)) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 2,402kB
3. 2,331.891 30,462.953 ↓ 1.5 20,912 1

Merge Left Join (cost=58,855.76..137,373.14 rows=14,413 width=52) (actual time=26,695.227..30,462.953 rows=20,912 loops=1)

  • Merge Cond: ((o.generated_order_id)::text = (orec.generated_order_id)::text)
4. 2,151.144 21,742.296 ↓ 1.5 20,912 1

Merge Left Join (cost=58,855.34..98,564.93 rows=14,413 width=45) (actual time=19,534.269..21,742.296 rows=20,912 loops=1)

  • Merge Cond: ((o.generated_order_id)::text = (m.generated_order_id)::text)
5. 352.528 2,453.613 ↓ 1.5 20,912 1

Sort (cost=18,357.26..18,393.29 rows=14,413 width=37) (actual time=2,296.425..2,453.613 rows=20,912 loops=1)

  • Sort Key: o.generated_order_id
  • Sort Method: quicksort Memory: 2,402kB
6. 1,185.282 2,101.085 ↓ 1.5 20,912 1

Nested Loop (cost=2,437.70..17,361.68 rows=14,413 width=37) (actual time=55.996..2,101.085 rows=20,912 loops=1)

  • Join Filter: (((timezone((c.timezone)::text, timezone('utc'::text, o.ordered_at)))::date >= '2020-06-01'::date) AND ((timezone((c.timezone)::text, timezone('utc'::text, o.ordered_at)))::date < '2020-07-01'::date))
  • Rows Removed by Join Filter: 107,702
7. 47.524 47.524 ↑ 1.0 1 1

Seq Scan on companies c (cost=0.00..1.18 rows=1 width=40) (actual time=47.510..47.524 rows=1 loops=1)

  • Filter: (id = 14)
  • Rows Removed by Filter: 13
8. 861.191 868.279 ↑ 1.0 128,614 1

Bitmap Heap Scan on orders o (cost=2,437.70..13,469.11 rows=129,713 width=45) (actual time=7.576..868.279 rows=128,614 loops=1)

  • Recheck Cond: (company_id = 14)
  • Heap Blocks: exact=3,585
9. 7.088 7.088 ↑ 1.0 128,614 1

Bitmap Index Scan on index_orders_on_company_id (cost=0.00..2,405.27 rows=129,713 width=0) (actual time=7.082..7.088 rows=128,614 loops=1)

  • Index Cond: (company_id = 14)
10. 3,811.528 17,137.539 ↓ 1.1 253,791 1

Finalize GroupAggregate (cost=40,498.08..77,183.30 rows=228,365 width=26) (actual time=8,236.328..17,137.539 rows=253,791 loops=1)

  • Group Key: m.generated_order_id
11. 2,952.911 13,326.011 ↓ 1.1 284,094 1

Gather Merge (cost=40,498.08..73,568.06 rows=266,318 width=26) (actual time=8,236.269..13,326.011 rows=284,094 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
12. 1,423.805 10,373.100 ↑ 1.4 95,332 3 / 3

Partial GroupAggregate (cost=39,498.05..41,828.34 rows=133,159 width=26) (actual time=8,194.534..10,373.100 rows=95,332 loops=3)

  • Group Key: m.generated_order_id
13. 2,124.415 8,949.295 ↑ 1.3 101,728 3 / 3

Sort (cost=39,498.05..39,830.95 rows=133,159 width=26) (actual time=8,194.474..8,949.295 rows=101,728 loops=3)

  • Sort Key: m.generated_order_id
  • Sort Method: external merge Disk: 4,232kB
  • Worker 0: Sort Method: external merge Disk: 4,336kB
  • Worker 1: Sort Method: external merge Disk: 4,344kB
14. 2,827.420 6,824.880 ↑ 1.3 106,527 3 / 3

Parallel Hash Join (cost=10,944.08..24,975.86 rows=133,159 width=26) (actual time=5,802.634..6,824.880 rows=106,527 loops=3)

  • Hash Cond: ((p.generated_payment_id)::text = (m.generated_payment_id)::text)
15. 1,558.244 1,558.244 ↑ 1.2 127,163 3 / 3

Parallel Seq Scan on payments p (cost=0.00..10,399.53 rows=158,953 width=27) (actual time=5.762..1,558.244 rows=127,163 loops=3)

16. 1,330.096 2,439.216 ↑ 1.3 106,527 3 / 3

Parallel Hash (cost=8,238.59..8,238.59 rows=133,159 width=37) (actual time=2,439.210..2,439.216 rows=106,527 loops=3)

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,456kB
17. 1,109.120 1,109.120 ↑ 1.3 106,527 3 / 3

Parallel Seq Scan on matches m (cost=0.00..8,238.59 rows=133,159 width=37) (actual time=11.524..1,109.120 rows=106,527 loops=3)

18. 4,167.422 6,388.766 ↑ 1.1 274,734 1

GroupAggregate (cost=0.42..34,923.09 rows=292,158 width=49) (actual time=0.117..6,388.766 rows=274,734 loops=1)

  • Group Key: orec.generated_order_id
19. 2,221.344 2,221.344 ↑ 1.1 274,735 1

Index Scan using index_order_reconciliations_on_generated_order_id on order_reconciliations orec (cost=0.42..30,540.72 rows=292,158 width=28) (actual time=0.078..2,221.344 rows=274,735 loops=1)

Planning time : 20.085 ms
Execution time : 31,109.787 ms