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

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

  • Group Key: a._accession_key, a.accid, a.prefixpart, a.numericpart, a._logicaldb_key, a._object_key, a._mgitype_key, a.private, a.preferred, a._createdby_key, a._modifiedby_key, a.creation_date, a.modification_date, a.accid, s.strain, (((((s.strain || ' '::text) || a1.symbol) || ','::text) || a2.symbol)), (((a1.symbol || ','::text) || a2.symbol)), l.name
  • Buffers: shared hit=1,581,092 read=101,336
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=1,581,092 read=101,336
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=1,581,092 read=101,336
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=1,127,515 read=81,193
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=788,733 read=80,693
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=377,978 read=78,426
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=1,175 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=1,171 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=376,803 read=78,422
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=376,803 read=78,422
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=135,748 read=78,400
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=135,147 read=78,250
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=135,144 read=78,250
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=241,055 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=410,755 read=2,267
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=338,782 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=18,585 read=2,769
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=14,471 read=2,767
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=10,745 read=2,371
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=5,218 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=4,089 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=1,129 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=1,125 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=5,527 read=2,069
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=3,726 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=4,114 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=434,992 read=17,374
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=362,135 read=16,496
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: 1,632kB
  • Buffers: shared hit=8 read=1,168
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=1,120
  • Buffers: shared hit=4 read=1,168
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=362,127 read=15,328
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=362,127 read=15,328
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=121,488 read=14,891
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=121,302 read=14,326
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=121,300 read=14,325
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: 6,838kB
  • 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=240,639 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=72,857 read=878
Planning time : 9.64 ms
Execution time : 1,873.825 ms