explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Co4b

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 2,230.977 ↓ 2.1 46 1

Limit (cost=203,161.62..203,164.19 rows=22 width=537) (actual time=2,230.941..2,230.977 rows=46 loops=1)

  • Buffers: shared hit=33175, temp read=15560 written=15770
  • Execution Time: 2308.878 ms(130 строк)
2. 82.622 2,308.432 ↓ 2.1 46 1

Gather Merge (cost=203,161.62..203,164.19 rows=22 width=537) (actual time=2,230.939..2,308.432 rows=46 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=99210, temp read=47036 written=47166
3. 0.075 2,225.810 ↓ 1.4 15 3 / 3

Sort (cost=202,161.60..202,161.62 rows=11 width=537) (actual time=2,225.807..2,225.810 rows=15 loops=3)

  • Sort Key: reg.id
  • Sort Method: quicksort Memory: 39kB
  • Worker 0: Sort Method: quicksort Memory: 31kB
  • Worker 1: Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=99210, temp read=47036 written=47166
4. 17.008 2,225.735 ↓ 1.4 15 3 / 3

Hash Semi Join (cost=138,513.84..202,161.41 rows=11 width=537) (actual time=1,894.372..2,225.735 rows=15 loops=3)

  • Hash Cond: (denorm.id = cvdreg_1.pid)
  • Buffers: shared hit=99194, temp read=47036 written=47166
5. 39.638 2,208.605 ↑ 1.2 167,759 3 / 3

Hash Left Join (cost=138,123.67..201,220.25 rows=209,698 width=610) (actual time=1,876.763..2,208.605 rows=167,759 loops=3)

  • Hash Cond: (main_1.disease_severity = ref_disease_severity.id)
  • Buffers: shared hit=98944, temp read=47036 written=47166
6. 38.908 2,168.948 ↑ 1.2 167,759 3 / 3

Hash Left Join (cost=138,122.53..200,282.97 rows=209,698 width=582) (actual time=1,876.728..2,168.948 rows=167,759 loops=3)

  • Hash Cond: (denorm_1.diagnosis_postmortem = cr_diagnosis_postmortem.id)
  • Buffers: shared hit=98941, temp read=47036 written=47166
7. 40.373 2,123.285 ↑ 1.2 167,759 3 / 3

Hash Left Join (cost=137,060.63..198,670.61 rows=209,698 width=459) (actual time=1,869.951..2,123.285 rows=167,759 loops=3)

  • Hash Cond: (cvdreg.disease_outcome = cr_disease_outcome.id)
  • Buffers: shared hit=96763, temp read=47036 written=47166
8. 51.958 2,082.890 ↑ 1.2 167,759 3 / 3

Hash Join (cost=137,059.47..197,970.22 rows=209,698 width=411) (actual time=1,869.914..2,082.890 rows=167,759 loops=3)

  • Hash Cond: ((cvdreg.diagnosis_main)::text = (cr_diagnosis_main.mkb)::text)
  • Buffers: shared hit=96760, temp read=47036 written=47166
9. 314.575 2,030.903 ↑ 1.2 167,759 3 / 3

Parallel Hash Left Join (cost=137,057.62..197,356.52 rows=209,698 width=297) (actual time=1,869.847..2,030.903 rows=167,759 loops=3)

  • Hash Cond: (denorm_1.last_daily = main_1.id)
  • Buffers: shared hit=96723, temp read=47036 written=47166
10. 137.385 1,522.945 ↑ 1.2 167,759 3 / 3

Parallel Hash Join (cost=86,874.34..126,510.78 rows=209,698 width=299) (actual time=1,098.895..1,522.945 rows=167,759 loops=3)

  • Hash Cond: (denorm.last_cvdreg = cvdreg.id)
  • Buffers: shared hit=65146, temp read=21979 written=22046
11. 40.081 999.296 ↑ 1.2 167,759 3 / 3

Hash Join (cost=43,210.91..81,187.25 rows=209,697 width=277) (actual time=711.593..999.296 rows=167,759 loops=3)

  • Hash Cond: (main.gender = p_gender.id)
  • Buffers: shared hit=39898, temp read=17614 written=17681
12. 39.580 959.191 ↑ 1.2 167,759 3 / 3

Hash Join (cost=43,209.84..79,858.10 rows=209,697 width=258) (actual time=711.498..959.191 rows=167,759 loops=3)

  • Hash Cond: (r_mo.regionid = r_reg.id)
  • Buffers: shared hit=39865, temp read=17614 written=17681
13. 56.821 919.570 ↑ 1.2 167,759 3 / 3

Parallel Hash Join (cost=43,205.77..79,278.23 rows=209,697 width=218) (actual time=711.443..919.570 rows=167,759 loops=3)

  • Hash Cond: (reg.mo = r_mo.id)
  • Buffers: shared hit=39859, temp read=17614 written=17681
14. 213.276 856.950 ↑ 1.2 167,759 3 / 3

Parallel Hash Join (cost=38,540.59..74,062.47 rows=209,697 width=160) (actual time=705.552..856.950 rows=167,759 loops=3)

  • Hash Cond: (reg.person = main.id)
  • Buffers: shared hit=35455, temp read=17614 written=17681
15. 134.097 543.583 ↑ 1.2 167,759 3 / 3

Hash Join (cost=16,688.64..42,099.06 rows=209,697 width=68) (actual time=375.933..543.583 rows=167,759 loops=3)

  • Hash Cond: (reg.id = denorm.id)
  • Buffers: shared hit=24936, temp read=5649 written=5649
16. 35.295 35.295 ↑ 1.2 167,759 3 / 3

Parallel Seq Scan on reg (cost=0.00..18,305.97 rows=209,697 width=52) (actual time=0.006..35.295 rows=167,759 loops=3)

  • Buffers: shared hit=16209
17. 337.368 374.191 ↓ 1.0 503,278 3 / 3

Hash (cost=7,939.73..7,939.73 rows=503,273 width=16) (actual time=374.191..374.191 rows=503,278 loops=3)

  • Buckets: 524288 Batches: 2 Memory Usage: 15898kB
  • Buffers: shared hit=8721, temp written=3315
18. 36.823 36.823 ↓ 1.0 503,278 3 / 3

Seq Scan on reg0denorm denorm (cost=0.00..7,939.73 rows=503,273 width=16) (actual time=0.017..36.823 rows=503,278 loops=3)

  • Buffers: shared hit=8721
19. 62.088 100.091 ↑ 1.8 168,438 3 / 3

Parallel Hash (cost=13,491.42..13,491.42 rows=297,242 width=100) (actual time=100.091..100.091 rows=168,438 loops=3)

  • Buckets: 131072 Batches: 8 Memory Usage: 9152kB
  • Buffers: shared hit=10519, temp written=6336
20. 38.003 38.003 ↑ 1.8 168,438 3 / 3

Parallel Seq Scan on person main (cost=0.00..13,491.42 rows=297,242 width=100) (actual time=0.007..38.003 rows=168,438 loops=3)

  • Buffers: shared hit=10519
21. 1.857 5.799 ↑ 1.8 6,578 3 / 3

Parallel Hash (cost=4,520.08..4,520.08 rows=11,608 width=62) (actual time=5.799..5.799 rows=6,578 loops=3)

  • Buckets: 32768 Batches: 1 Memory Usage: 2176kB
  • Buffers: shared hit=4404
22. 3.942 3.942 ↑ 1.8 6,578 3 / 3

Parallel Seq Scan on fnsi_1_2_643_5_1_13_13_11_1461 r_mo (cost=0.00..4,520.08 rows=11,608 width=62) (actual time=0.008..3.942 rows=6,578 loops=3)

  • Buffers: shared hit=4404
23. 0.020 0.041 ↑ 1.0 92 3 / 3

Hash (cost=2.92..2.92 rows=92 width=44) (actual time=0.040..0.041 rows=92 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
  • Buffers: shared hit=6
24. 0.021 0.021 ↑ 1.0 92 3 / 3

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_206 r_reg (cost=0.00..2.92 rows=92 width=44) (actual time=0.010..0.021 rows=92 loops=3)

  • Buffers: shared hit=6
25. 0.005 0.024 ↑ 1.0 3 3 / 3

Hash (cost=1.03..1.03 rows=3 width=23) (actual time=0.023..0.024 rows=3 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
26. 0.019 0.019 ↑ 1.0 3 3 / 3

Seq Scan on fnsi_1_2_643_5_1_13_13_11_1040 p_gender (cost=0.00..1.03 rows=3 width=23) (actual time=0.018..0.019 rows=3 loops=3)

  • Buffers: shared hit=3
27. 60.095 386.264 ↑ 1.2 167,892 3 / 3

Parallel Hash (cost=41,040.14..41,040.14 rows=209,863 width=38) (actual time=386.264..386.264 rows=167,892 loops=3)

  • Buckets: 524288 Batches: 1 Memory Usage: 35200kB
  • Buffers: shared hit=25248, temp read=4365 written=4365
28. 133.834 326.169 ↑ 1.2 167,892 3 / 3

Hash Left Join (cost=17,742.62..41,040.14 rows=209,863 width=38) (actual time=149.391..326.169 rows=167,892 loops=3)

  • Hash Cond: (cvdreg.id = denorm_1.id)
  • Buffers: shared hit=25248, temp read=4365 written=4365
29. 43.900 43.900 ↑ 1.2 167,892 3 / 3

Parallel Seq Scan on cvdreg (cost=0.00..16,924.63 rows=209,863 width=26) (actual time=0.006..43.900 rows=167,892 loops=3)

  • Buffers: shared hit=14826
30. 90.256 148.435 ↓ 1.0 503,677 3 / 3

Hash (cost=8,494.72..8,494.72 rows=503,672 width=20) (actual time=148.435..148.435 rows=503,677 loops=3)

  • Buckets: 262144 Batches: 2 Memory Usage: 12765kB
  • Buffers: shared hit=10374, temp written=2901
31. 58.179 58.179 ↓ 1.0 503,677 3 / 3

Seq Scan on cvdreg0denorm denorm_1 (cost=0.00..8,494.72 rows=503,672 width=20) (actual time=0.016..58.179 rows=503,677 loops=3)

  • Buffers: shared hit=10374
32. 103.589 193.383 ↑ 1.3 543,316 3 / 3

Parallel Hash (cost=38,371.79..38,371.79 rows=679,479 width=14) (actual time=193.383..193.383 rows=543,316 loops=3)

  • Buckets: 524288 Batches: 8 Memory Usage: 13696kB
  • Buffers: shared hit=31577, temp written=6316
33. 89.794 89.794 ↑ 1.3 543,316 3 / 3

Parallel Seq Scan on cvdreg_daily main_1 (cost=0.00..38,371.79 rows=679,479 width=14) (actual time=0.010..89.794 rows=543,316 loops=3)

  • Buffers: shared hit=31577
34. 0.014 0.029 ↑ 1.0 38 3 / 3

Hash (cost=1.38..1.38 rows=38 width=119) (actual time=0.029..0.029 rows=38 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
  • Buffers: shared hit=3
35. 0.015 0.015 ↑ 1.0 38 3 / 3

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_568 cr_diagnosis_main (cost=0.00..1.38 rows=38 width=119) (actual time=0.010..0.015 rows=38 loops=3)

  • Buffers: shared hit=3
36. 0.004 0.022 ↑ 1.0 7 3 / 3

Hash (cost=1.07..1.07 rows=7 width=52) (actual time=0.022..0.022 rows=7 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
37. 0.018 0.018 ↑ 1.0 7 3 / 3

Seq Scan on fnsi_1_2_643_5_1_13_13_99_2_558 cr_disease_outcome (cost=0.00..1.07 rows=7 width=52) (actual time=0.017..0.018 rows=7 loops=3)

  • Buffers: shared hit=3
38. 3.998 6.755 ↑ 1.0 14,929 3 / 3

Hash (cost=875.29..875.29 rows=14,929 width=127) (actual time=6.755..6.755 rows=14,929 loops=3)

  • Buckets: 16384 Batches: 1 Memory Usage: 2499kB
  • Buffers: shared hit=2178
39. 2.757 2.757 ↑ 1.0 14,929 3 / 3

Seq Scan on fnsi_1_2_643_5_1_13_13_11_1005 cr_diagnosis_postmortem (cost=0.00..875.29 rows=14,929 width=127) (actual time=0.021..2.757 rows=14,929 loops=3)

  • Buffers: shared hit=2178
40. 0.007 0.019 ↑ 1.0 6 3 / 3

Hash (cost=1.06..1.06 rows=6 width=32) (actual time=0.019..0.019 rows=6 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=3
41. 0.012 0.012 ↑ 1.0 6 3 / 3

Seq Scan on fnsi_1_2_643_5_1_13_13_11_1006 ref_disease_severity (cost=0.00..1.06 rows=6 width=32) (actual time=0.012..0.012 rows=6 loops=3)

  • Buffers: shared hit=3
42. 0.012 0.122 ↓ 1.8 46 3 / 3

Hash (cost=389.85..389.85 rows=26 width=8) (actual time=0.122..0.122 rows=46 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=160
43. 0.009 0.110 ↓ 1.8 46 3 / 3

Nested Loop (cost=6.38..389.85 rows=26 width=8) (actual time=0.053..0.110 rows=46 loops=3)

  • Buffers: shared hit=160
44. 0.022 0.022 ↑ 1.0 1 3 / 3

Index Scan using i4fnsi_1_2_643_5_1_13_13_11_14618oid on fnsi_1_2_643_5_1_13_13_11_1461 mo (cost=0.41..4.43 rows=1 width=4) (actual time=0.022..0.022 rows=1 loops=3)

  • Index Cond: ((oid)::text = ANY ('{1.2.643.5.1.13.13.12.2.25.2293}'::text[]))
  • Buffers: shared hit=14
45. 0.062 0.079 ↑ 4.3 46 3 / 3

Bitmap Heap Scan on cvdreg cvdreg_1 (cost=5.97..383.42 rows=200 width=20) (actual time=0.027..0.079 rows=46 loops=3)

  • Recheck Cond: (mo = mo.id)
  • Heap Blocks: exact=44
  • Buffers: shared hit=146
46. 0.017 0.017 ↑ 4.0 50 3 / 3

Bitmap Index Scan on i4cvdreg8mo (cost=0.00..5.92 rows=200 width=0) (actual time=0.017..0.017 rows=50 loops=3)

  • Index Cond: (mo = mo.id)
  • Buffers: shared hit=11
Planning time : 10.824 ms