explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fLabw

Settings
# exclusive inclusive rows x rows loops node
1. 28.339 31,515.401 ↑ 1,711.4 9 1

GroupAggregate (cost=130,899.41..132,478.25 rows=15,403 width=52) (actual time=31,464.065..31,515.401 rows=9 loops=1)

  • Group Key: o.company_id, orders.state, (max((orec.auto_reconciliation_status)::text)), (CASE WHEN (((max(p.available_at)) >= '2020-06-01 04:00:00'::timestamp without time zone) AND ((max(p.available_at)) < '2020-07-01 03:59:59'::timestamp without time zone)) THEN true ELSE false END)
  • Filter: (sum(abs(o.total_tax)) > '0'::double precision)
  • Rows Removed by Filter: 103
  • JIT:
  • Functions: 81
  • Options: Inlining false, Optimization false, Expressions true, Deforming true
  • Timing: Generation 11.818 ms, Inlining 0.000 ms, Optimization 4.438 ms, Emission 68.393 ms, Total 84.649 ms
2. 59.483 31,487.062 ↑ 12.5 3,709 1

Sort (cost=130,899.41..131,014.93 rows=46,210 width=52) (actual time=31,461.525..31,487.062 rows=3,709 loops=1)

  • Sort Key: orders.state, (max((orec.auto_reconciliation_status)::text)), (CASE WHEN (((max(p.available_at)) >= '2020-06-01 04:00:00'::timestamp without time zone) AND ((max(p.available_at)) < '2020-07-01 03:59:59'::timestamp without time zone)) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 386kB
3. 2,241.705 31,427.579 ↑ 12.5 3,709 1

Merge Left Join (cost=48,402.16..127,319.08 rows=46,210 width=52) (actual time=13,080.598..31,427.579 rows=3,709 loops=1)

  • Merge Cond: ((o.generated_order_id)::text = (orec.generated_order_id)::text)
4. 2,000.031 22,273.307 ↑ 2.2 3,709 1

Merge Left Join (cost=48,401.74..88,030.78 rows=8,032 width=44) (actual time=11,833.594..22,273.307 rows=3,709 loops=1)

  • Merge Cond: ((o.generated_order_id)::text = (m.generated_order_id)::text)
5. 68.498 889.705 ↓ 2.1 3,709 1

Sort (cost=7,903.66..7,908.13 rows=1,786 width=36) (actual time=861.379..889.705 rows=3,709 loops=1)

  • Sort Key: o.generated_order_id
  • Sort Method: quicksort Memory: 386kB
6. 97.671 821.207 ↓ 2.1 3,709 1

Nested Loop Left Join (cost=305.32..7,807.20 rows=1,786 width=36) (actual time=58.488..821.207 rows=3,709 loops=1)

7. 193.564 430.525 ↓ 2.1 3,709 1

Nested Loop (cost=304.89..2,447.38 rows=1,786 width=33) (actual time=57.549..430.525 rows=3,709 loops=1)

  • Join Filter: (((timezone((c.timezone)::text, timezone('utc'::text, o.processed_at)))::date >= '2020-06-01'::date) AND ((timezone((c.timezone)::text, timezone('utc'::text, o.processed_at)))::date < '2020-07-01'::date))
  • Rows Removed by Join Filter: 12,408
8. 42.081 42.081 ↑ 1.0 1 1

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

  • Filter: (id = 14)
  • Rows Removed by Filter: 13
9. 182.263 194.880 ↓ 1.0 16,117 1

Bitmap Heap Scan on order_refunds o (cost=304.89..1,963.87 rows=16,078 width=41) (actual time=14.513..194.880 rows=16,117 loops=1)

  • Recheck Cond: (company_id = 14)
  • Heap Blocks: exact=457
10. 12.617 12.617 ↓ 1.0 16,117 1

Bitmap Index Scan on index_order_refunds_on_company_id (cost=0.00..300.88 rows=16,078 width=0) (actual time=12.611..12.617 rows=16,117 loops=1)

  • Index Cond: (company_id = 14)
11. 293.011 293.011 ↑ 1.0 1 3,709

Index Scan using index_orders_on_generated_order_id on orders (cost=0.42..3.00 rows=1 width=21) (actual time=0.079..0.079 rows=1 loops=3,709)

  • Index Cond: ((generated_order_id)::text = (o.generated_order_id)::text)
12. 3,986.949 19,383.571 ↓ 1.1 253,235 1

Finalize GroupAggregate (cost=40,498.08..77,183.30 rows=228,365 width=26) (actual time=10,070.287..19,383.571 rows=253,235 loops=1)

  • Group Key: m.generated_order_id
13. 3,177.356 15,396.622 ↓ 1.1 283,348 1

Gather Merge (cost=40,498.08..73,568.06 rows=266,318 width=26) (actual time=10,070.222..15,396.622 rows=283,348 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
14. 1,507.353 12,219.266 ↑ 1.4 94,985 3 / 3

Partial GroupAggregate (cost=39,498.05..41,828.34 rows=133,159 width=26) (actual time=9,912.926..12,219.266 rows=94,985 loops=3)

  • Group Key: m.generated_order_id
15. 2,501.001 10,711.913 ↑ 1.3 101,409 3 / 3

Sort (cost=39,498.05..39,830.95 rows=133,159 width=26) (actual time=9,912.869..10,711.913 rows=101,409 loops=3)

  • Sort Key: m.generated_order_id
  • Sort Method: external merge Disk: 4,080kB
  • Worker 0: Sort Method: external merge Disk: 4,256kB
  • Worker 1: Sort Method: external merge Disk: 4,584kB
16. 3,397.631 8,210.912 ↑ 1.3 106,527 3 / 3

Parallel Hash Join (cost=10,944.08..24,975.86 rows=133,159 width=26) (actual time=7,156.352..8,210.912 rows=106,527 loops=3)

  • Hash Cond: ((p.generated_payment_id)::text = (m.generated_payment_id)::text)
17. 1,741.449 1,741.449 ↑ 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=9.366..1,741.449 rows=127,163 loops=3)

18. 1,780.318 3,071.832 ↑ 1.3 106,527 3 / 3

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

  • Buckets: 65,536 Batches: 8 Memory Usage: 3,424kB
19. 1,291.514 1,291.514 ↑ 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=25.787..1,291.514 rows=106,527 loops=3)

20. 4,498.375 6,912.567 ↑ 1.1 274,162 1

GroupAggregate (cost=0.42..34,923.09 rows=292,158 width=49) (actual time=0.062..6,912.567 rows=274,162 loops=1)

  • Group Key: orec.generated_order_id
21. 2,414.192 2,414.192 ↑ 1.1 274,163 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.032..2,414.192 rows=274,163 loops=1)

Planning time : 60.100 ms
Execution time : 31,666.261 ms