explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g8tQ

Settings
# exclusive inclusive rows x rows loops node
1. 727.202 10,128.857 ↓ 8.8 1,318 1

Nested Loop (cost=159,285.06..159,696.48 rows=149 width=76) (actual time=9,356.507..10,128.857 rows=1,318 loops=1)

2.          

CTE pair_history

3. 3.353 9,373.703 ↓ 8.8 1,318 1

Finalize GroupAggregate (cost=159,266.20..159,284.64 rows=149 width=24) (actual time=9,355.582..9,373.703 rows=1,318 loops=1)

  • Group Key: o_1.id, e.mean_id, e.pair_start
4. 64.154 9,370.350 ↓ 17.1 2,120 1

Gather Merge (cost=159,266.20..159,281.91 rows=124 width=24) (actual time=9,355.569..9,370.350 rows=2,120 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 4.110 9,306.196 ↓ 11.4 707 3 / 3

Partial GroupAggregate (cost=158,266.18..158,267.57 rows=62 width=24) (actual time=9,301.810..9,306.196 rows=707 loops=3)

  • Group Key: o_1.id, e.mean_id, e.pair_start
6. 1.201 9,302.086 ↓ 13.4 833 3 / 3

Sort (cost=158,266.18..158,266.33 rows=62 width=24) (actual time=9,301.802..9,302.086 rows=833 loops=3)

  • Sort Key: o_1.id, e.mean_id, e.pair_start
  • Sort Method: quicksort Memory: 83kB
  • Worker 0: Sort Method: quicksort Memory: 81kB
  • Worker 1: Sort Method: quicksort Memory: 63kB
7. 307.956 9,300.885 ↓ 13.4 833 3 / 3

Hash Join (cost=314.60..158,264.33 rows=62 width=24) (actual time=438.732..9,300.885 rows=833 loops=3)

  • Hash Cond: ((o_1.agency_id)::numeric = a.id)
8. 529.617 8,985.221 ↓ 5.6 186,255 3 / 3

Nested Loop (cost=0.98..157,858.83 rows=33,384 width=28) (actual time=1.402..8,985.221 rows=186,255 loops=3)

9. 3,985.484 3,985.484 ↓ 5.6 186,255 3 / 3

Parallel Index Scan using es_event_pairing_idx on es_event e (cost=0.56..100,958.46 rows=33,384 width=24) (actual time=1.321..3,985.484 rows=186,255 loops=3)

  • Index Cond: ((name = ANY ('{ORDER_IS_PAIR,ORDER_IS_UNPAIR}'::text[])) AND (pair_start >= '2019-12-01 00:00:00'::timestamp without time zone))
10. 4,470.120 4,470.120 ↑ 1.0 1 558,765 / 3

Index Scan using es_order_pk on es_order o_1 (cost=0.43..1.70 rows=1 width=8) (actual time=0.024..0.024 rows=1 loops=558,765)

  • Index Cond: (id = e.order_id)
11. 0.022 7.708 ↑ 1.0 9 3 / 3

Hash (cost=313.50..313.50 rows=9 width=5) (actual time=7.707..7.708 rows=9 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
12. 7.686 7.686 ↑ 1.0 9 3 / 3

Seq Scan on agencies a (cost=0.00..313.50 rows=9 width=5) (actual time=2.460..7.686 rows=9 loops=3)

  • Filter: (organization_id = '5660'::numeric)
  • Rows Removed by Filter: 5206
13. 9,375.295 9,375.295 ↓ 8.8 1,318 1

CTE Scan on pair_history ph (cost=0.00..2.98 rows=149 width=24) (actual time=9,355.586..9,375.295 rows=1,318 loops=1)

14. 26.360 26.360 ↑ 1.0 1 1,318

Index Scan using es_order_pk on es_order o (cost=0.43..2.43 rows=1 width=956) (actual time=0.020..0.020 rows=1 loops=1,318)

  • Index Cond: (id = ph.order_id)
Planning time : 0.809 ms
Execution time : 10,129.693 ms