explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cx6H

Settings
# exclusive inclusive rows x rows loops node
1. 98.521 9,134.537 ↑ 4.6 465,396 1

Nested Loop (cost=158,582.68..268,786.71 rows=2,149,257 width=16) (actual time=8,452.952..9,134.537 rows=465,396 loops=1)

2. 0.562 8,458.348 ↓ 6.5 966 1

Nested Loop (cost=158,582.25..158,830.68 rows=149 width=24) (actual time=8,445.753..8,458.348 rows=966 loops=1)

3. 1.217 8,449.092 ↓ 6.5 966 1

Finalize GroupAggregate (cost=158,581.82..158,600.26 rows=149 width=24) (actual time=8,445.718..8,449.092 rows=966 loops=1)

  • Group Key: o_1.id, e.mean_id, e.pair_start
4. 52.368 8,447.875 ↓ 13.5 1,675 1

Gather Merge (cost=158,581.82..158,597.53 rows=124 width=24) (actual time=8,445.706..8,447.875 rows=1,675 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 0.517 8,395.507 ↓ 9.0 558 3 / 3

Partial GroupAggregate (cost=157,581.80..157,583.19 rows=62 width=24) (actual time=8,394.779..8,395.507 rows=558 loops=3)

  • Group Key: o_1.id, e.mean_id, e.pair_start
6. 1.086 8,394.990 ↓ 10.5 648 3 / 3

Sort (cost=157,581.80..157,581.95 rows=62 width=24) (actual time=8,394.769..8,394.990 rows=648 loops=3)

  • Sort Key: o_1.id, e.mean_id, e.pair_start
  • Sort Method: quicksort Memory: 75kB
  • Worker 0: Sort Method: quicksort Memory: 65kB
  • Worker 1: Sort Method: quicksort Memory: 55kB
7. 158.487 8,393.904 ↓ 10.5 648 3 / 3

Hash Join (cost=314.60..157,579.95 rows=62 width=24) (actual time=513.450..8,393.904 rows=648 loops=3)

  • Hash Cond: ((o_1.agency_id)::numeric = a.id)
8. 307.843 8,231.406 ↓ 4.9 162,956 3 / 3

Nested Loop (cost=0.98..157,174.45 rows=33,384 width=28) (actual time=0.196..8,231.406 rows=162,956 loops=3)

9. 4,990.355 4,990.355 ↓ 4.9 162,956 3 / 3

Parallel Index Scan using es_event_pairing_idx on es_event e (cost=0.56..100,575.46 rows=33,384 width=24) (actual time=0.106..4,990.355 rows=162,956 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. 2,933.208 2,933.208 ↑ 1.0 1 488,868 / 3

Index Scan using es_order_pk on es_order o_1 (cost=0.42..1.70 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=488,868)

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

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

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

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

  • Filter: (organization_id = '5660'::numeric)
  • Rows Removed by Filter: 5173
13. 8.694 8.694 ↑ 1.0 1 966

Index Only Scan using es_order_pk on es_order o (cost=0.42..1.54 rows=1 width=4) (actual time=0.009..0.009 rows=1 loops=966)

  • Index Cond: (id = o_1.id)
  • Heap Fetches: 42
14. 62.790 577.668 ↑ 1.8 482 966

Append (cost=0.43..729.20 rows=876 width=12) (actual time=0.055..0.598 rows=482 loops=966)

15. 7.728 7.728 ↓ 0.0 0 966

Index Only Scan using _hyper_1_248_chunk_position_reorder_chunk_idx on _hyper_1_248_chunk p (cost=0.43..88.21 rows=104 width=12) (actual time=0.008..0.008 rows=0 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
16. 4.830 4.830 ↓ 0.0 0 966

Index Only Scan using _hyper_1_213_chunk_position_reorder_chunk_idx on _hyper_1_213_chunk p_1 (cost=0.43..95.09 rows=114 width=12) (actual time=0.005..0.005 rows=0 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
17. 3.864 3.864 ↓ 0.0 0 966

Index Only Scan using _hyper_1_207_chunk_position_reorder_chunk_idx on _hyper_1_207_chunk p_2 (cost=0.43..95.00 rows=114 width=12) (actual time=0.004..0.004 rows=0 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
18. 41.538 41.538 ↑ 3.4 28 966

Index Only Scan using _hyper_1_192_chunk_position_reorder_chunk_idx on _hyper_1_192_chunk p_3 (cost=0.43..74.83 rows=94 width=12) (actual time=0.006..0.043 rows=28 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 26893
19. 2.898 2.898 ↓ 0.0 0 966

Index Only Scan using _hyper_1_270_chunk_position_reorder_chunk_idx on _hyper_1_270_chunk p_4 (cost=0.42..16.19 rows=21 width=12) (actual time=0.003..0.003 rows=0 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
20. 173.880 173.880 ↓ 2.2 197 966

Index Only Scan using _hyper_1_182_chunk_position_reorder_chunk_idx on _hyper_1_182_chunk p_5 (cost=0.43..75.49 rows=89 width=12) (actual time=0.010..0.180 rows=197 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 190659
21. 0.966 0.966 ↓ 0.0 0 966

Index Only Scan using _hyper_1_41_chunk_position_reorder_chunk_idx on _hyper_1_41_chunk p_6 (cost=0.12..0.17 rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
22. 3.864 3.864 ↓ 0.0 0 966

Index Only Scan using _hyper_1_202_chunk_position_reorder_chunk_idx on _hyper_1_202_chunk p_7 (cost=0.43..89.28 rows=111 width=12) (actual time=0.004..0.004 rows=0 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
23. 3.864 3.864 ↓ 0.0 0 966

Index Only Scan using _hyper_1_197_chunk_position_reorder_chunk_idx on _hyper_1_197_chunk p_8 (cost=0.43..63.21 rows=83 width=12) (actual time=0.004..0.004 rows=0 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 0
24. 119.784 119.784 ↓ 1.5 81 966

Index Only Scan using _hyper_1_178_chunk_position_reorder_chunk_idx on _hyper_1_178_chunk p_9 (cost=0.43..49.61 rows=53 width=12) (actual time=0.008..0.124 rows=81 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 78062
25. 151.662 151.662 ↓ 1.9 176 966

Index Only Scan using _hyper_1_187_chunk_position_reorder_chunk_idx on _hyper_1_187_chunk p_10 (cost=0.43..77.74 rows=92 width=12) (actual time=0.009..0.157 rows=176 loops=966)

  • Index Cond: ((mean_id = e.mean_id) AND (position_ts >= e.pair_start) AND (position_ts <= (max(e.pair_end))) AND (position_ts >= '2019-12-01 00:00:00'::timestamp without time zone))
  • Heap Fetches: 169782
Planning time : 2.553 ms
Execution time : 9,162.646 ms