explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XPaV

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 6,323.498 ↑ 4.5 44 1

GroupAggregate (cost=628,756.38..724,680.3 rows=200 width=20) (actual time=6,061.729..6,323.498 rows=44 loops=1)

  • Group Key: pos.ref_date
  • Buffers: shared hit=2,512,709 read=221,838
2. 132.428 6,342.518 ↑ 3.6 220 1

Gather Merge (cost=628,756.38..724,672.3 rows=800 width=20) (actual time=6,055.746..6,342.518 rows=220 loops=1)

  • Workers Planned: 4
  • Workers Launched: 4
  • Buffers: shared hit=12,512,338 read=1,043,141
3. 24.691 6,210.090 ↑ 4.5 44 5 / 5

GroupAggregate (cost=627,756.33..723,576.95 rows=200 width=20) (actual time=5,944.731..6,210.09 rows=44 loops=5)

  • Group Key: pos.ref_date
  • Buffers: shared hit=12,512,338 read=1,043,141
4. 123.388 6,185.399 ↑ 1.1 246,588 5 / 5

Merge Join (cost=627,756.33..721,610.2 rows=261,967 width=12) (actual time=5,938.627..6,185.399 rows=246,588 loops=5)

  • Buffers: shared hit=12,512,338 read=1,043,141
5. 63.536 5,623.279 ↑ 1.1 246,588 5 / 5

Sort (cost=627,755.9..628,410.81 rows=261,967 width=16) (actual time=5,614.136..5,623.279 rows=246,588 loops=5)

  • Sort Key: pos.ref_date, pos.undrly_id
  • Sort Method: quicksort Memory: 23,342kB
  • Buffers: shared hit=6,478,997 read=1,043,141
6. 159.907 5,559.743 ↑ 1.1 246,588 5 / 5

Merge Join (cost=510,326.27..604,180.14 rows=261,967 width=16) (actual time=5,221.689..5,559.743 rows=246,588 loops=5)

  • Buffers: shared hit=6,478,967 read=1,043,139
7. 144.519 4,912.201 ↑ 1.1 246,588 5 / 5

Sort (cost=510,325.84..510,980.76 rows=261,967 width=20) (actual time=4,888.178..4,912.201 rows=246,588 loops=5)

  • Sort Key: pos.ref_date, pos.asset_id
  • Sort Method: quicksort Memory: 28,159kB
  • Buffers: shared hit=432,280 read=1,038,937
8. 124.541 4,767.682 ↑ 1.1 246,588 5 / 5

Merge Join (cost=459,042.46..486,750.09 rows=261,967 width=20) (actual time=4,514.161..4,767.682 rows=246,588 loops=5)

  • Buffers: shared hit=432,280 read=1,038,937
9. 148.835 4,526.885 ↑ 1.1 246,588 5 / 5

Sort (cost=459,042.04..459,696.96 rows=261,967 width=24) (actual time=4,513.501..4,526.885 rows=246,588 loops=5)

  • Sort Key: pos.bp_id, pos.ref_date
  • Sort Method: quicksort Memory: 28,159kB
  • Buffers: shared hit=417,959 read=1,036,204
10. 44.010 4,378.050 ↑ 1.1 246,588 5 / 5

Nested Loop (cost=1,926.48..435,466.29 rows=261,967 width=24) (actual time=15.902..4,378.05 rows=246,588 loops=5)

  • Buffers: shared hit=417,959 read=1,036,204
11. 298.638 307.330 ↑ 1.3 35,635 5 / 5

Bitmap Heap Scan on met_pfm_cont cont (cost=1,926.48..122,739.03 rows=44,708 width=8) (actual time=14.622..307.33 rows=35,635 loops=5)

  • Heap Blocks: exact=25,100
  • Buffers: shared read=116,240
12. 8.692 8.692 ↑ 1.0 178,173 1 / 5

Bitmap Index Scan on met_pfm_cont_s_adv_pm_id (cost=0..1,881.77 rows=178,833 width=0) (actual time=43.461..43.461 rows=178,173 loops=1)

  • Index Cond: (cont.adv_pm_id = 6,862,048)
  • Buffers: shared read=489
13. 178.173 4,026.710 ↑ 10.0 7 178,173 / 5

Append (cost=0..6.29 rows=70 width=28) (actual time=0.011..0.113 rows=7 loops=178,173)

  • Buffers: shared hit=417,959 read=919,964
14. 0.000 0.000 ↓ 0.0 0 178,173 / 5

Seq Scan on met_pfm_pos_p pos (cost=0..0 rows=1 width=28) (actual time=0..0 rows=0 loops=178,173)

  • Filter: ((pos.ref_date >= '2018-05-01'::date) AND (pos.ref_date <= '2018-06-29'::date) AND (cont.ref_date = pos.ref_date) AND (cont.cont_id = pos.cont_id))
15. 2,423.153 2,423.153 ↑ 9.8 4 178,173 / 5

Index Scan using met_pfm_pos_p_2018m05_ref_date_cont_id on met_pfm_pos_2018m05 pos_1 (cost=0.43..3.41 rows=39 width=28) (actual time=0.004..0.068 rows=4 loops=178,173)

  • Index Cond: ((pos_1.ref_date = cont.ref_date) AND (pos_1.ref_date >= '2018-05-01'::date) AND (pos_1.ref_date <= '2018-06-29'::date) AND (pos_1.cont_id = cont.cont_id))
  • Buffers: shared hit=220,188 read=504,943
16. 1,425.384 1,425.384 ↑ 10.0 3 178,173 / 5

Index Scan using met_pfm_pos_p_2018m06_ref_date_cont_id on met_pfm_pos_2018m06 pos_2 (cost=0.43..2.89 rows=30 width=28) (actual time=0.004..0.04 rows=3 loops=178,173)

  • Index Cond: ((pos_2.ref_date = cont.ref_date) AND (pos_2.ref_date >= '2018-05-01'::date) AND (pos_2.ref_date <= '2018-06-29'::date) AND (pos_2.cont_id = cont.cont_id))
  • Buffers: shared hit=197,771 read=415,021
17. 116.256 116.256 ↓ 1.2 1,237,322 5 / 5

Index Only Scan using met_pfm_bp_bp_id_ref_date on met_pfm_bp bp (cost=0.42..18,143.95 rows=1,006,875 width=8) (actual time=0.533..116.256 rows=1,237,322 loops=5)

  • Buffers: shared hit=14,321 read=2,733
18. 487.635 487.635 ↑ 2.2 1,756,504 5 / 5

Index Only Scan using met_pfm_asset_s_ref_date_asset_id on met_pfm_asset ins (cost=0.43..70,415.59 rows=3,908,431 width=8) (actual time=0.435..487.635 rows=1,756,504 loops=5)

  • Buffers: shared hit=6,046,687 read=4,202
19. 438.732 438.732 ↑ 2.5 1,533,786 5 / 5

Index Only Scan using met_pfm_asset_s_ref_date_asset_id on met_pfm_asset undly_ins (cost=0.43..70,415.59 rows=3,908,431 width=8) (actual time=0.039..438.732 rows=1,533,786 loops=5)

  • Buffers: shared hit=6,033,341
Planning time : 3.5 ms
Execution time : 6,349.668 ms