explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ucHR

Settings
# exclusive inclusive rows x rows loops node
1. 217.330 1,868.774 ↓ 292.9 104,554 1

Aggregate (cost=83,756.44..83,760.01 rows=357 width=272) (actual time=1,836.654..1,868.774 rows=104,554 loops=1)

  • Buffers: shared hit=1581092 read=101336
2. 787.811 1,651.444 ↓ 292.9 104,570 1

Gather (cost=11,801.97..83,740.37 rows=357 width=272) (actual time=89.897..1,651.444 rows=104,570 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=1581092 read=101336
3. 2.634 863.633 ↓ 134.1 34,857 3 / 3

Append (cost=10,801.97..82,704.67 rows=260 width=272) (actual time=66.406..863.633 rows=34,857 loops=3)

  • Buffers: shared hit=1581092 read=101336
4. 15.815 578.249 ↓ 256.9 42,388 2 / 3

Nested Loop (cost=14,705.28..22,232.75 rows=165 width=178) (actual time=38.925..867.374 rows=42,388 loops=2)

  • Buffers: shared hit=1127515 read=81193
5. 2.147 528.035 ↓ 312.7 51,599 2 / 3

Nested Loop (cost=14,704.85..21,479.71 rows=165 width=128) (actual time=38.922..792.052 rows=51,599 loops=2)

  • Buffers: shared hit=788733 read=80693
6. 13.454 457.089 ↓ 312.7 51,599 2 / 3

Merge Join (cost=14,704.43..20,729.15 rows=165 width=112) (actual time=38.904..685.634 rows=51,599 loops=2)

  • Buffers: shared hit=377978 read=78426
7. 11.029 14.071 ↑ 1.2 51,599 2 / 3

Sort (cost=6,604.92..6,756.68 rows=60,705 width=12) (actual time=16.603..21.107 rows=51,599 loops=2)

  • Sort Key: ap._genotype_key
  • Buffers: shared hit=1175 read=4
8. 3.043 3.043 ↑ 1.2 51,599 2 / 3

Seq Scan on gxd_allelepair ap (cost=0..1,782.05 rows=60,705 width=12) (actual time=0.003..4.564 rows=51,599 loops=2)

  • Buffers: shared hit=1171 read=4
9. 7.170 429.564 ↓ 479.7 104,570 1 / 3

Materialize (cost=8,099.51..81,463.84 rows=218 width=108) (actual time=44.571..1,288.691 rows=104,570 loops=1)

  • Buffers: shared hit=376803 read=78422
10. 10.542 422.393 ↓ 368.5 80,330 1 / 3

Nested Loop (cost=8,099.51..81,463.3 rows=218 width=108) (actual time=44.567..1,267.18 rows=80,330 loops=1)

  • Buffers: shared hit=376803 read=78422
11. 7.677 385.075 ↓ 368.5 80,330 1 / 3

Merge Join (cost=8,099.22..80,511.1 rows=218 width=81) (actual time=44.55..1,155.224 rows=80,330 loops=1)

  • Buffers: shared hit=135748 read=78400
12. 7.545 360.289 ↓ 18.3 80,330 1 / 3

Nested Loop (cost=0.56..911,328.69 rows=4,379 width=73) (actual time=0.039..1,080.868 rows=80,330 loops=1)

  • Buffers: shared hit=135147 read=78250
13. 352.744 352.744 ↓ 18.3 80,330 1 / 3

Index Scan using acc_accession_idx_clustered on acc_accession a (cost=0.56..911,268.47 rows=4,379 width=63) (actual time=0.021..1,058.232 rows=80,330 loops=1)

  • Index Cond: (a._mgitype_key = 12)
  • Filter: ((a._logicaldb_key = 1) AND (a.preferred = 1) AND (a.prefixpart = 'MGI:'::text))
  • Buffers: shared hit=135144 read=78250
14. 0.000 0.000 ↑ 1.0 1 80,330 / 3

Materialize (cost=0..5.48 rows=1 width=14) (actual time=0..0 rows=1 loops=80,330)

  • Buffers: shared hit=3
15. 0.007 0.007 ↑ 1.0 1 1 / 3

Seq Scan on acc_logicaldb l (cost=0..5.47 rows=1 width=14) (actual time=0.012..0.021 rows=1 loops=1)

  • Filter: (l._logicaldb_key = 1)
  • Buffers: shared hit=3
16. 13.029 17.108 ↑ 1.0 80,330 1 / 3

Sort (cost=8,098.65..8,299.47 rows=80,330 width=8) (actual time=44.507..51.325 rows=80,330 loops=1)

  • Sort Key: g._genotype_key
  • Buffers: shared hit=601 read=150
17. 4.079 4.079 ↑ 1.0 80,330 1 / 3

Seq Scan on gxd_genotype g (cost=0..1,554.3 rows=80,330 width=8) (actual time=0.005..12.238 rows=80,330 loops=1)

  • Buffers: shared hit=601 read=150
18. 26.777 26.777 ↑ 1.0 1 80,330 / 3

Index Scan using prb_strain_pkey on prb_strain s (cost=0.29..4.37 rows=1 width=35) (actual time=0.001..0.001 rows=1 loops=80,330)

  • Index Cond: (s._strain_key = g._strain_key)
  • Buffers: shared hit=241055 read=22
19. 68.799 68.799 ↑ 1.0 1 103,198 / 3

Index Scan using all_allele_pkey on all_allele a1 (cost=0.42..4.55 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=103,198)

  • Index Cond: (a1._allele_key = ap._allele_key_1)
  • Buffers: shared hit=410755 read=2267
20. 34.399 34.399 ↑ 1.0 1 103,198 / 3

Index Scan using all_allele_pkey on all_allele a2 (cost=0.42..4.55 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=103,198)

  • Index Cond: (a2._allele_key = ap._allele_key_2)
  • Buffers: shared hit=338782 read=500
21. 0.335 50.791 ↓ 124.7 1,372 1 / 3

Nested Loop (cost=10,801.97..44,169.12 rows=11 width=177) (actual time=32.897..152.372 rows=1,372 loops=1)

  • Buffers: shared hit=18585 read=2769
22. 0.183 46.340 ↓ 124.7 1,372 1 / 3

Nested Loop (cost=10,801.97..44,108.79 rows=11 width=94) (actual time=32.874..139.019 rows=1,372 loops=1)

  • Buffers: shared hit=14471 read=2767
23. 0.374 42.498 ↓ 124.7 1,372 1 / 3

Nested Loop (cost=10,801.68..44,062.84 rows=11 width=67) (actual time=32.861..127.494 rows=1,372 loops=1)

  • Buffers: shared hit=10745 read=2371
24. 4.546 22.001 ↑ 3.0 1,372 1 / 3

Merge Join (cost=10,801.11..20,798.44 rows=4,161 width=8) (actual time=32.817..66.004 rows=1,372 loops=1)

  • Buffers: shared hit=5218 read=302
25. 4.898 4.898 ↓ 2.4 80,330 1 / 3

Index Scan using gxd_genotype_pkey on gxd_genotype g_1 (cost=0.29..8,967.96 rows=33,471 width=8) (actual time=0.027..14.693 rows=80,330 loops=1)

  • Buffers: shared hit=4089 read=252
26. 9.367 12.558 ↑ 1.0 103,198 1 / 3

Sort (cost=10,800.82..11,058.82 rows=103,198 width=4) (actual time=32.786..37.674 rows=103,198 loops=1)

  • Sort Key: ap_1._genotype_key
  • Buffers: shared hit=1129 read=50
27. 3.191 3.191 ↑ 1.0 103,198 1 / 3

Seq Scan on gxd_allelepair ap_1 (cost=0..2,206.98 rows=103,198 width=4) (actual time=0.005..9.572 rows=103,198 loops=1)

  • Buffers: shared hit=1125 read=50
28. 20.123 20.123 ↑ 1.0 1 1,372 / 3

Index Scan using acc_accession_idx_clustered on acc_accession a_1 (cost=0.56..5.58 rows=1 width=63) (actual time=0.027..0.044 rows=1 loops=1,372)

  • Index Cond: ((a_1._object_key = g_1._genotype_key) AND (a_1._mgitype_key = 12))
  • Filter: ((a_1._logicaldb_key = 1) AND (a_1.preferred = 1) AND (a_1.prefixpart = 'MGI:'::text))
  • Buffers: shared hit=5527 read=2069
29. 3.659 3.659 ↑ 1.0 1 1,372 / 3

Index Scan using prb_strain_pkey on prb_strain s_1 (cost=0.29..4.18 rows=1 width=35) (actual time=0.008..0.008 rows=1 loops=1,372)

  • Index Cond: (s_1._strain_key = g_1._strain_key)
  • Buffers: shared hit=3726 read=396
30. 4.116 4.116 ↑ 1.0 1 1,372 / 3

Seq Scan on acc_logicaldb l_1 (cost=0..5.47 rows=1 width=14) (actual time=0.001..0.009 rows=1 loops=1,372)

  • Filter: (l_1._logicaldb_key = 1)
  • Buffers: shared hit=4114 read=2
31. 1.445 231.959 ↓ 317.6 9,211 2 / 3

Nested Loop (cost=10,399.81..16,297.93 rows=29 width=166) (actual time=44.257..347.939 rows=9,211 loops=2)

  • Buffers: shared hit=434992 read=17374
32. 4.776 212.092 ↓ 317.6 9,211 2 / 3

Merge Join (cost=10,399.39..16,162.9 rows=29 width=108) (actual time=44.252..318.138 rows=9,211 loops=2)

  • Buffers: shared hit=362135 read=16496
33. 2.357 10.944 ↑ 1.2 9,211 2 / 3

Sort (cost=2,299.88..2,326.82 rows=10,777 width=8) (actual time=15.279..16.416 rows=9,211 loops=2)

  • Sort Key: ap_2._genotype_key
  • Sort Method: quicksort Memory: 1632kB
  • Buffers: shared hit=8 read=1168
34. 7.936 8.587 ↑ 1.2 9,211 2 / 3

Bitmap Heap Scan on gxd_allelepair ap_2 (cost=295.28..1,578.05 rows=10,777 width=8) (actual time=1.08..12.88 rows=9,211 loops=2)

  • Heap Blocks: exact=1120
  • Buffers: shared hit=4 read=1168
35. 0.651 0.651 ↓ 1.0 18,422 1 / 3

Bitmap Index Scan on gxd_allelepair_idx_allele_key_2 (cost=0..290.7 rows=18,321 width=0) (actual time=1.953..1.953 rows=18,422 loops=1)

  • Index Cond: (ap_2._allele_key_2 IS NULL)
  • Buffers: shared hit=2 read=50
36. 6.660 196.372 ↓ 377.1 82,208 1 / 3

Materialize (cost=8,099.51..81,316.41 rows=218 width=108) (actual time=52.22..589.116 rows=82,208 loops=1)

  • Buffers: shared hit=362127 read=15328
37. 15.578 189.712 ↓ 368.5 80,330 1 / 3

Nested Loop (cost=8,099.51..81,315.87 rows=218 width=108) (actual time=52.216..569.135 rows=80,330 loops=1)

  • Buffers: shared hit=362127 read=15328
38. 7.809 147.357 ↓ 368.5 80,330 1 / 3

Merge Join (cost=8,099.22..80,384.14 rows=218 width=81) (actual time=52.206..442.07 rows=80,330 loops=1)

  • Buffers: shared hit=121488 read=14891
39. 8.420 119.915 ↓ 18.3 80,330 1 / 3

Nested Loop (cost=0.56..909,721.84 rows=4,379 width=73) (actual time=0.071..359.745 rows=80,330 loops=1)

  • Buffers: shared hit=121302 read=14326
40. 111.495 111.495 ↓ 18.3 80,330 1 / 3

Index Scan using acc_accession_idx_clustered on acc_accession a_2 (cost=0.56..909,661.63 rows=4,379 width=63) (actual time=0.057..334.484 rows=80,330 loops=1)

  • Index Cond: (a_2._mgitype_key = 12)
  • Filter: ((a_2._logicaldb_key = 1) AND (a_2.preferred = 1) AND (a_2.prefixpart = 'MGI:'::text))
  • Buffers: shared hit=121300 read=14325
41. 0.000 0.000 ↑ 1.0 1 80,330 / 3

Materialize (cost=0..5.48 rows=1 width=14) (actual time=0..0 rows=1 loops=80,330)

  • Buffers: shared hit=2 read=1
42. 0.008 0.008 ↑ 1.0 1 1 / 3

Seq Scan on acc_logicaldb l_2 (cost=0..5.47 rows=1 width=14) (actual time=0.011..0.024 rows=1 loops=1)

  • Filter: (l_2._logicaldb_key = 1)
  • Buffers: shared hit=2 read=1
43. 13.167 19.633 ↑ 1.0 80,330 1 / 3

Sort (cost=8,098.65..8,299.47 rows=80,330 width=8) (actual time=52.126..58.898 rows=80,330 loops=1)

  • Sort Key: g_2._genotype_key
  • Sort Method: quicksort Memory: 6838kB
  • Buffers: shared hit=186 read=565
44. 6.466 6.466 ↑ 1.0 80,330 1 / 3

Seq Scan on gxd_genotype g_2 (cost=0..1,554.3 rows=80,330 width=8) (actual time=0.004..19.398 rows=80,330 loops=1)

  • Buffers: shared hit=186 read=565
45. 26.777 26.777 ↑ 1.0 1 80,330 / 3

Index Scan using prb_strain_pkey on prb_strain s_2 (cost=0.29..4.27 rows=1 width=35) (actual time=0.001..0.001 rows=1 loops=80,330)

  • Index Cond: (s_2._strain_key = g_2._strain_key)
  • Buffers: shared hit=240639 read=437
46. 18.422 18.422 ↑ 1.0 1 18,422 / 3

Index Scan using all_allele_pkey on all_allele a1_1 (cost=0.42..4.65 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=18,422)

  • Index Cond: (a1_1._allele_key = ap_2._allele_key_1)
  • Buffers: shared hit=72857 read=878
Planning time : 9.64 ms
Execution time : 1,873.825 ms