explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZwAj

Settings
# exclusive inclusive rows x rows loops node
1. 0.086 3,121.822 ↑ 1.0 1 1

Aggregate (cost=10,648.04..10,648.05 rows=1 width=0) (actual time=3,121.822..3,121.822 rows=1 loops=1)

2. 132.147 3,121.736 ↓ 162.0 324 1

Nested Loop Left Join (cost=1,099.91..10,648.03 rows=2 width=0) (actual time=23.529..3,121.736 rows=324 loops=1)

  • Join Filter: (et.linkid = ct.id)
  • Rows Removed by Join Filter: 2,217,838
3. 24.086 226.517 ↓ 324.0 324 1

Nested Loop Left Join (cost=292.30..9,538.22 rows=1 width=4) (actual time=13.425..226.517 rows=324 loops=1)

  • Join Filter: (ct.id = cse.controlid)
  • Rows Removed by Join Filter: 461,211
4. 16.722 131.151 ↓ 324.0 324 1

Nested Loop Left Join (cost=213.20..9,410.35 rows=1 width=4) (actual time=11.487..131.151 rows=324 loops=1)

  • Join Filter: (actionlink.linkid = ce.id)
  • Rows Removed by Join Filter: 324,324
5. 0.334 82.353 ↓ 324.0 324 1

Nested Loop Left Join (cost=3.00..9,168.43 rows=1 width=8) (actual time=10.423..82.353 rows=324 loops=1)

6. 52.976 81.047 ↓ 324.0 324 1

Nested Loop (cost=2.72..9,168.12 rows=1 width=8) (actual time=10.396..81.047 rows=324 loops=1)

7. 0.332 14.139 ↓ 324.0 324 1

Nested Loop (cost=0.84..8,796.76 rows=1 width=12) (actual time=10.111..14.139 rows=324 loops=1)

8. 11.863 11.863 ↓ 324.0 324 1

Index Scan using complianceentry_compliancefrequencyid_endofperiod_idx on complianceentry ce (cost=0.42..8,788.30 rows=1 width=12) (actual time=10.074..11.863 rows=324 loops=1)

  • Index Cond: (endofperiod >= '2020-07-01'::date)
  • Filter: (controlcompleted IS NULL)
  • Rows Removed by Filter: 13
9. 1.944 1.944 ↑ 1.0 1 324

Index Scan using pk_compfreq on compliancefrequency cf (cost=0.42..8.44 rows=1 width=20) (actual time=0.005..0.006 rows=1 loops=324)

  • Index Cond: (id = ce.compliancefrequencyid)
10. 13.932 13.932 ↑ 1.0 1 324

Index Only Scan using businessunit_pkey on businessunit bu (cost=1.87..371.35 rows=1 width=4) (actual time=0.021..0.043 rows=1 loops=324)

  • Index Cond: ((id = cf.businessunitid) AND (id = ANY ('{9722,7642,5575,8966,5841,6820,5542,5549,7340,5566,5567,5568,8481,5569,5557,8741,6605,6202,7000,5570,6606,7441,6340,5576,5577,6203,8968,7244,5893,8967,5843,6205,6460,6608,6206,7243,5550,8881,5551,5558,5571,5560,5572,8745,5559,8480,6607,8969,7402,8746,5578,5552,7060,5561,6360,5579,7624,5580,5581,7181,6603,7400,8621,7021,5553,6740,8808,5326,5324,5325,6264,7401,5327,6204,5562,8960,5554,5582,5323,6720,5555,5541,7764,6581,5842,5556,5563,8743,5574,5564,5565,8540,8742,7403,7462,7442,2159,1878,2048,1974,1916,9340,9660,9360,1502,8914,6545,2286,8580,5140,4440,4475,9960,9046,8581,7188,7187,7189,7191,7190,7192,7195,7193,7194,7196,5403,2293,5524,2310,4396,7380,4561,8842,4421,4402,6322,7046,6222,4469,2347,5341,9741,2315,5131,2329,4439,2349,2324,5985,8760,2335,2360,8683,4414,2303,2325,2327,4416,6162,8500,9184,8686,9760,4821,5144,2341,8802,8961,4382,6783,8681,7600,4422,8720,6002,4478,9880,7020,4389,8747,2295,5360,4394,4430,2306,2355,6380,7186,7540,6760,5013,6160,6260,7761,2354,9386,7360,2330,9860,2309,8620,5720,2357,9820,6226,2332,8640,8682,2319,2358,4525,9382,2331,6621,2336,5820,4438,8721,2366,5320,2359,8800,4562,8748,4721,2361,8861,2364,4661,2365,2308,4682,8722,8684,2356,2320,6104,7560,4321,4436,6043,4431,2353,4411,5260,4940,7161,7620,8905,4418,6120,2346,5529,8971,9182,9181,9180,7740,6660,4429,2352,4437,9041,4460,8912,4472,6610,6700,7760,4574,5941,4408,5289,4684,9183,2333,2337,2312,2340,2294,4169,2328,2350,2296,9383,4410,4398,6622,2344,5339,5328,6804,9002,9902,4500,8804,7320,9683,2237,2267,4000,9621,9562,9721,9260,5480,9981,9980,10000,1788,6941,4764,8900,2248,4291,4292,5001,4293,4294,5000,5982,2244,4120,4279,5060,5265,7260,7421,6600,7423,7422,7424,7420,6601,6822,6823,6824,6921,6825,2250,5989,8520,2246,5400,4276,4277,6082,10003,2243,9342,9500,9020,2264,6382,9021,5545,5744,2241,2240,1877,6960,5546,2239,9261,10002,9380,2238,5322,2284,2242,4001,6281,9940,9823,2273,2280,9822,5723,7247,9942,2277,9941,8906,8908,8907,8910,8901,9051,8501,9580,9411,2251,9720,8660,2265,2266,6940,9527,9400,9533,9444,4400,4420,4427,9740,4388,6782,4405,4477,4393,9040,9601,9482,9507,9479,9424,9403,9510,9426,9427,9437,9402,9432,9511,9529,9530,9515,9528,9518,9428,9506,9406,9401,9440,9438,9780,9524,9442,9407,9421,9441,9508,9423,9531,9516,9433,9522,9405,9439,9519,9526,9521,9420,9446,9448,9483,9484,9435,9469,9456,9449,9472,9477,9727,9458,9474,9478,9464,9485,9468,9452,9462,9470,9455,9463,9466,9451,9465,9450,9476,9701,9471,9461,9447,9453,9454,9459,9460,9457,9475,9473,9703,9467,9505,9512,9517,9445,9681,9525,9509,9404,9501,9821,9523,9434,9782,9481,9781,9480,9600,9534,9425,9704,9861,9422,9436,8642,8482,8970,8809,5421,4274,9901,5988,9800,10001,8801,6040,8641,8923,9641,8460,9725,7741,8972,5528,5547,2255,6500,5267,6542,2263,4425,4473,7198,7197,7202,4729,4395,4468,4726,7641,4725,4381,6680,4413,4432,4409,4415,7643,4385,7640,6520,4426,4383,4386,4524,4391,4428,6540,4401,5424,6620,4731,4384,4820,5821,4407,4727,6101,4728,4403,5401,4760,4580,4404,4387,7204,7203,4660,5262,4424,4732,5522,4434,4392,4417,4419,5901,5526,4605,6761,4471,4476,4406,4900,5202,6326,4397,9101,9384,5622,5644,5609,8964,5585,5623,5597,6785,5586,5624,5598,5599,5681,9100,5645,5600,5601,9102,9103,9104,9105,9581,5676,9106,9107,5587,5588,5654,9108,9109,5675,5655,9110,5610,5625,9111,8700,5611,5602,5677,9112,5656,7645,9640,9113,9582,9114,9726,9115,7773,5633,9116,9117,5634,5589,5667,5612,6882,6892,6898,6899,6885,6900,8962,7765,7766,7120,6895,6888,6881,7767,8963,5635,5636,5637,9158,5646,5668,9320,5638,5647,5590,5626,9050,9119,5627,9121,5613,7720,7771,6604,9122,9123,5657,8965,9124,9943,9052,5639,5614,7768,7582,9125,9126,9127,5615,5616,5658,9128,9120,5669,7445,5679,9201,5629,9130,9131,5659,7772,9132,7162,5780,5603,5660,9133,9134,5591,9135,9136,9137,5662,9138,5604,5678,9139,9118,9140,9141,8761,5648,9280,9142,5680,5605,9143,9200,5592,7443,5649,5650,5617,9144,5630,9145,9146,9147,7581,5584,8807,5608,9080,9081,9084,5674,8806,9085,9086,5666,5621,5632,9082,5643,9083,9560,9561,5653,5583,9061,9063,9060,9062,9148,5640,5682,5593,9149,5663,9150,5670,9151,9387,5618,5671,9053,7770,9152,5641,5642,9153,9154,9155,9129,5606,9156,5672,5683,9157,5619,5684,9159,5620,9160,9161,9162,5651,5664,6420,9163,9164,5631,9165,5652,9166,5673,9167,5665,5594,5595,7769,5607,5420,9341,8820,9410,4148,1782,1692,4172,8723,1778,4920,4171,1838,1631,1544,4159,1779,5501,1845,1569,1601,1748,1557,4175,4194,1534,8920,1696,4231,1770,1726,1856,1688,4216,1667,1592,1817,1613,4206,5141,4195,9003,1565,5269,4207,1750,8922,1610,1708,8780,1614,4196,4232,8782,4208,8740,7164,8980,4168,6124,4173,4209,1699,1541,4178,1772,1787,1577,4233,4234,4160,4720,7100,1539,1538,4235,4149,1689,1670,6220,1563,1598,1642,4983,8784,1643,1754,1594,4217,1701,1703,1553,1548,1607,1639,4960,9620,1640,9761,1707,1547,7647,8860,8921,5220,4197,1620,8783,9742,1609,1674,4161,1580,4221,1771,1777,1641,6803,1600,4210,1573,1729,7280,1780,4150,1537,4180,5382,1599,9920,4222,6683,1611,4174,4576,7743,1709,1785,4181,1596,1578,4162,7361,1704,4177,4198,8880,4179,4824,8680,1671,7444,4569,1758,1669,7480,1617,1623,9840,1756,1749,4199,4163,7661,1711,1702,1635,1536,1672,4200,4151,1571,1731,1730,1673,1572,4182,1559,4183,4236,4201,1595,4140,5945,4202,6224,4211,1556,1546,1550,1786,1781,4184,7440,1691,1757,1597,1776,1668,8701,1675,4237,4245,1624,4224,1575,1793,1798,1803,1564,1697,1732,1713,4225,1602,4152,4203,1698,1604,8785,4241,7580,7763,4153,4212,1735,6780,1690,5380,4226,1608,1566,4176,1775,1676,1618,8840,9642,4238,4227,4242,1840,1825,1864,1860,1837,1814,1834,1857,5080,1846,1826,1829,1819,1855,1844,1861,1839,4466,1822,1835,1821,4523,4521,1827,8560,1847,4522,1850,1858,1818,1824,1833,9304,9388,1859,1851,6122,1862,1832,1848,4503,1831,1863,1823,1841,1849,1843,1853,1842,1865,1694,4213,1710,1560,4157,1603,1727,9042,4239,1773,6200,4204,4681,1540,1807,4191,5800,4243,6080,8600,1533,4103,1579,1568,4228,1784,1695,5760,9221,9389,1574,1543,7742,1606,1728,8904,4229,1570,4185,1753,7762,1562,1693,6262,4158,1615,1535,7160,4244,4205,4186,4155,5330,1774,1558,1542,1751,4164,4165,1755,1612,4188,4190,1567,4230,1551,9540,1700,1549,4214,4192,1576,6000,1605,1622,4166,4215,1619,1616,4240,1621,4189,4218,1712,1733,4156,4193,5521,1552,4167,1593,7660,1783,6781,7621,4501,2252,9001,7301,9408,5321,8803,5846,5896,5895,5548,8911,7800,5164,6321,6280,1163,6980,9220,9186,1875,9680,1626,1164,7500,1,9724,8913,2257,7300,9302,9300,9303,9301,7121,5184,9047,7245,9900,4502,2247,9043,6681,7220,9049,6702,7700,9185}'::integer[])))
  • Heap Fetches: 324
11. 0.972 0.972 ↑ 1.0 1 324

Index Scan using control_pkey on control ct (cost=0.28..0.30 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=324)

  • Index Cond: (cf.controlid = id)
12. 31.437 32.076 ↓ 1.0 1,001 324

HashAggregate (cost=210.20..219.96 rows=976 width=8) (actual time=0.003..0.099 rows=1,001 loops=324)

13. 0.509 0.639 ↑ 1.0 1,057 1

Bitmap Heap Scan on actionlink (cost=32.55..204.87 rows=1,066 width=8) (actual time=0.145..0.639 rows=1,057 loops=1)

  • Recheck Cond: ((linktable)::text = 'complianceentry'::text)
14. 0.130 0.130 ↑ 1.0 1,060 1

Bitmap Index Scan on idx_al_linktable (cost=0.00..32.28 rows=1,066 width=0) (actual time=0.130..0.130 rows=1,060 loops=1)

  • Index Cond: ((linktable)::text = 'complianceentry'::text)
15. 70.625 71.280 ↓ 1.0 1,424 324

HashAggregate (cost=79.11..96.52 rows=1,393 width=28) (actual time=0.006..0.220 rows=1,424 loops=324)

16. 0.290 0.655 ↓ 1.0 1,484 1

Hash Join (cost=29.33..71.84 rows=1,453 width=28) (actual time=0.258..0.655 rows=1,484 loops=1)

  • Hash Cond: (cse.riskeventid = re_1.id)
17. 0.132 0.132 ↓ 1.0 1,484 1

Seq Scan on controlriskeventlink cse (cost=0.00..22.53 rows=1,453 width=8) (actual time=0.011..0.132 rows=1,484 loops=1)

18. 0.112 0.233 ↓ 1.0 638 1

Hash (cost=21.37..21.37 rows=637 width=28) (actual time=0.233..0.233 rows=638 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 39kB
19. 0.121 0.121 ↓ 1.0 638 1

Seq Scan on riskevent re_1 (cost=0.00..21.37 rows=637 width=28) (actual time=0.006..0.121 rows=638 loops=1)

20. 2,643.516 2,763.072 ↓ 1.0 6,846 324

GroupAggregate (cost=807.61..962.85 rows=6,532 width=36) (actual time=0.022..8.528 rows=6,846 loops=324)

21. 115.410 119.556 ↓ 1.0 7,994 324

Sort (cost=807.61..826.70 rows=7,634 width=36) (actual time=0.018..0.369 rows=7,994 loops=324)

  • Sort Key: et.linkid
  • Sort Method: quicksort Memory: 588kB
22. 1.818 4.146 ↓ 1.0 7,994 1

Hash Left Join (cost=42.89..315.29 rows=7,634 width=36) (actual time=1.598..4.146 rows=7,994 loops=1)

  • Hash Cond: (et.tagid = tagselect.id)
  • Join Filter: ((et.linktable)::text = 'control'::text)
  • Rows Removed by Join Filter: 7,338
23. 0.750 0.750 ↓ 1.0 7,994 1

Seq Scan on entitytag et (cost=0.00..148.34 rows=7,634 width=20) (actual time=0.007..0.750 rows=7,994 loops=1)

24. 0.076 1.578 ↓ 1.0 416 1

Hash (cost=37.72..37.72 rows=414 width=36) (actual time=1.578..1.578 rows=416 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 28kB
25. 0.032 1.502 ↓ 1.0 416 1

Subquery Scan on tagselect (cost=32.54..37.72 rows=414 width=36) (actual time=1.451..1.502 rows=416 loops=1)

26. 1.286 1.470 ↓ 1.0 416 1

Sort (cost=32.54..33.58 rows=414 width=52) (actual time=1.450..1.470 rows=416 loops=1)

  • Sort Key: (((tt.name || ':'::text) || t.value))
  • Sort Method: quicksort Memory: 59kB
27. 0.127 0.184 ↓ 1.0 416 1

Hash Left Join (cost=1.58..14.55 rows=414 width=52) (actual time=0.040..0.184 rows=416 loops=1)

  • Hash Cond: (t.typeid = tt.id)
28. 0.035 0.035 ↓ 1.0 416 1

Seq Scan on tag t (cost=0.00..7.14 rows=414 width=24) (actual time=0.007..0.035 rows=416 loops=1)

29. 0.006 0.022 ↓ 2.0 53 1

Hash (cost=1.26..1.26 rows=26 width=36) (actual time=0.022..0.022 rows=53 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 3kB
30. 0.016 0.016 ↓ 2.0 53 1

Seq Scan on tagtype tt (cost=0.00..1.26 rows=26 width=36) (actual time=0.008..0.016 rows=53 loops=1)

Total runtime : 3,137.635 ms