explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g0i

Settings
# exclusive inclusive rows x rows loops node
1. 478.113 8,626.931 ↓ 8.8 1,318 1

Nested Loop (cost=159,285.48..159,694.67 rows=149 width=60) (actual time=8,125.637..8,626.931 rows=1,318 loops=1)

2.          

CTE pair_history

3. 2.511 8,130.517 ↓ 8.8 1,318 1

Finalize GroupAggregate (cost=159,266.62..159,285.05 rows=149 width=24) (actual time=8,124.407..8,130.517 rows=1,318 loops=1)

  • Group Key: o_1.id, e.mean_id, e.pair_start
4. 64.097 8,128.006 ↓ 16.5 2,050 1

Gather Merge (cost=159,266.62..159,282.32 rows=124 width=24) (actual time=8,124.390..8,128.006 rows=2,050 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.758 8,063.909 ↓ 11.0 683 3 / 3

Partial GroupAggregate (cost=158,266.59..158,267.99 rows=62 width=24) (actual time=8,062.909..8,063.909 rows=683 loops=3)

  • Group Key: o_1.id, e.mean_id, e.pair_start
6. 1.147 8,063.151 ↓ 13.4 833 3 / 3

Sort (cost=158,266.59..158,266.75 rows=62 width=24) (actual time=8,062.900..8,063.151 rows=833 loops=3)

  • Sort Key: o_1.id, e.mean_id, e.pair_start
  • Sort Method: quicksort Memory: 76kB
  • Worker 0: Sort Method: quicksort Memory: 68kB
  • Worker 1: Sort Method: quicksort Memory: 83kB
7. 221.096 8,062.004 ↓ 13.4 833 3 / 3

Hash Join (cost=314.60..158,264.75 rows=62 width=24) (actual time=455.886..8,062.004 rows=833 loops=3)

  • Hash Cond: ((o_1.agency_id)::numeric = a.id)
8. 434.282 7,834.876 ↓ 5.6 186,257 3 / 3

Nested Loop (cost=0.98..157,859.24 rows=33,384 width=28) (actual time=0.229..7,834.876 rows=186,257 loops=3)

9. 3,489.197 3,489.197 ↓ 5.6 186,257 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=0.141..3,489.197 rows=186,257 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. 3,911.397 3,911.397 ↑ 1.0 1 558,771 / 3

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

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

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

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

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

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

CTE Scan on pair_history ph (cost=0.00..2.98 rows=149 width=24) (actual time=8,124.410..8,131.684 rows=1,318 loops=1)

14. 17.134 17.134 ↑ 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.013..0.013 rows=1 loops=1,318)

  • Index Cond: (id = ph.order_id)
Planning time : 0.745 ms
Execution time : 8,627.578 ms