explain.depesz.com

PostgreSQL's explain analyze made readable

Result: oHcj

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 137.656 ↑ 1.1 15 1

Sort (cost=9,719.68..9,719.72 rows=16 width=861) (actual time=137.655..137.656 rows=15 loops=1)

  • Sort Key: p13.""IsTop"" DESC, p13.""HasContractWithH24"" DESC, p13.""LastName"", p13.""Id"", ""p.Doctors.DoctorQualifications"".""Doctor_Id"
  • Sort Method: quicksort Memory: 34kB
2. 0.014 137.636 ↑ 1.1 15 1

Hash Left Join (cost=9,627.69..9,719.36 rows=16 width=861) (actual time=137.609..137.636 rows=15 loops=1)

  • Hash Cond: (""d.Qualification"".""QualificationTypeRefId"" = ""d.Qualification.QualificationTypeRef"".""Id"")
3. 0.006 137.609 ↑ 1.1 15 1

Nested Loop (cost=9,626.51..9,718.11 rows=16 width=191) (actual time=137.585..137.609 rows=15 loops=1)

4. 0.002 137.588 ↑ 1.1 15 1

Nested Loop (cost=9,626.22..9,711.41 rows=16 width=34) (actual time=137.579..137.588 rows=15 loops=1)

5. 0.002 137.568 ↑ 1.7 6 1

Unique (cost=9,625.94..9,626.09 rows=10 width=26) (actual time=137.566..137.568 rows=6 loops=1)

6. 0.004 137.566 ↑ 1.7 6 1

Sort (cost=9,625.94..9,625.96 rows=10 width=26) (actual time=137.565..137.566 rows=6 loops=1)

  • Sort Key: ""p.Doctors1"".""Id"", p13.""IsTop"", p13.""HasContractWithH24"", p13.""LastName"", p13.""Id"
  • Sort Method: quicksort Memory: 25kB
7. 0.001 137.562 ↑ 1.7 6 1

Nested Loop Left Join (cost=9,540.03..9,625.77 rows=10 width=26) (actual time=137.542..137.562 rows=6 loops=1)

8. 0.005 137.549 ↑ 1.7 6 1

Nested Loop (cost=9,539.76..9,622.72 rows=10 width=26) (actual time=137.534..137.549 rows=6 loops=1)

9. 0.003 137.514 ↑ 1.0 10 1

Unique (cost=9,539.47..9,539.60 rows=10 width=22) (actual time=137.511..137.514 rows=10 loops=1)

10. 0.003 137.511 ↑ 1.0 10 1

Sort (cost=9,539.47..9,539.50 rows=10 width=22) (actual time=137.511..137.511 rows=10 loops=1)

  • Sort Key: p13.""Id"", p13.""IsTop"", p13.""HasContractWithH24"", p13.""LastName"
  • Sort Method: quicksort Memory: 25kB
11. 0.002 137.508 ↑ 1.0 10 1

Limit (cost=9,539.18..9,539.20 rows=10 width=22) (actual time=137.507..137.508 rows=10 loops=1)

12. 1.247 137.506 ↑ 323.7 10 1

Sort (cost=9,539.18..9,547.27 rows=3,237 width=22) (actual time=137.506..137.506 rows=10 loops=1)

  • Sort Key: p13.""IsTop"" DESC, p13.""HasContractWithH24"" DESC, p13.""LastName"", p13.""Id"
  • Sort Method: top-N heapsort Memory: 26kB
13. 1.105 136.259 ↓ 1.4 4,558 1

Hash Semi Join (cost=6,033.01..9,469.23 rows=3,237 width=22) (actual time=128.155..136.259 rows=4,558 loops=1)

  • Hash Cond: (p13.""Id"" = e58.""PartyId"")
14. 0.914 126.527 ↓ 1.3 4,565 1

Hash Semi Join (cost=4,993.77..8,300.13 rows=3,382 width=34) (actual time=119.474..126.527 rows=4,565 loops=1)

  • Hash Cond: (p13.""Id"" = e57.""PartyId"")
15. 0.995 18.690 ↑ 1.1 4,565 1

Hash Semi Join (cost=3,645.34..6,841.24 rows=5,198 width=30) (actual time=12.512..18.690 rows=4,565 loops=1)

  • Hash Cond: (p13.""Id"" = e56.""PartyId"")
16. 0.643 10.580 ↑ 1.4 4,622 1

Hash Join (cost=2,448.57..5,420.63 rows=6,312 width=26) (actual time=5.297..10.580 rows=4,622 loops=1)

  • Hash Cond: (p13.""LegalEntityId"" = l24.""Id"")
17. 1.996 9.454 ↑ 1.5 4,630 1

Hash Join (cost=2,211.23..5,165.03 rows=6,935 width=30) (actual time=4.713..9.454 rows=4,630 loops=1)

  • Hash Cond: (p13.""Id"" = e55.""PartyId"")
18. 2.905 2.905 ↑ 1.0 13,427 1

Index Scan using ""Parties_expr_idx"" on ""Parties"" p13 (cost=0.29..2,841.69 rows=13,427 width=36) (actual time=0.009..2.905 rows=13,427 loops=1)

19. 0.496 4.553 ↑ 2.0 4,725 1

Hash (cost=2,091.93..2,091.93 rows=9,521 width=4) (actual time=4.553..4.553 rows=4,725 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 295kB
20. 1.585 4.057 ↑ 2.0 4,725 1

HashAggregate (cost=1,996.72..2,091.93 rows=9,521 width=4) (actual time=3.560..4.057 rows=4,725 loops=1)

  • Group Key: e55.""PartyId"
21. 2.472 2.472 ↑ 1.3 8,125 1

Index Scan using ""Employees_IX_DivisionId"" on ""Employees"" e55 (cost=0.29..1,970.88 rows=10,334 width=4) (actual time=0.114..2.472 rows=8,125 loops=1)

  • Index Cond: ((""DivisionId"" IS NOT NULL) AND (""DivisionId"" = ANY ('{2468,1798,1489,1989,1560,1091,951,1128,2080,2128,1691,2009,2196,2108,1136,946,1831,1003,1045,1331,1307,1552,1589,1811,2125,2204,1175,1830,940,1963,1018,2276,2152,2040,1833,2025,1682,2172,1431,1595,1372,2082,815,2259,1004,1313,1510,1749,1368,2006,1857,1692,1705,1463,1782,1550,1162,1644,1841,1030,1694,1614,1239,1606,1312,1484,1984,1755,2063,2168,1602,2147,1662,2015,1118,1990,1025,1007,2116,1319,1776,1104,1975,1936,1603,1664,2183,1942,1992,1321,1522,1216,1685,1049,1006,1324,1008,1325,2099,1548,2198,1679,1952,977,1950,1507,2220,2127,1959,1549,1474,1616,1613,1845,1800,1629,1828,1009,2098,3138,2178,2073,1546,1816,1050,1349,1619,1836,1788,1523,1877,1815,1044,1062,1458,1823,1848,1862,1167,1697,1583,2171,2121,1144,2157,2150,1447,1646,1398,1866,1987,1022,1336,2218,1170,2214,1371,948,1540,1951,1250,1966,1378,1561,981,2149,1334,1780,2012,1126,2151,1678,984,1249,868,1870,1774,1748,947,1411,978,972,1084,1681,2130,2244,1337,2060,1209,1428,1837,1569,3300,1674,793,1793,1986,1048,1995,2230,1620,1770,2096,1570,2156,2093,1887,1146,1215,1544,1632,1210,1665,1476,2105,933,1238,1792,2143,2087,1354,1808,1120,1976,1574,1503,1306,1645,1961,1666,969,1847,1700,2049,1145,1791,1810,1974,1977,1856,2114,2053,1615,1248,1563,1567,2193,1219,967,1397,1564,2160,2126,1844,1579,1571,1565,2018,1668,1036,1301,2089,1878,1057,1467,2155,1046,2164,1328,1161,1016,1647,1684,1015,1661,1944,954,2129,1218,1639,2267,2153,2181,1021,1077,2145,1608,1281,1896,1247,1409,1427,1173,1658,1622,1508,974,1557,2074,1149,1370,1115,1401,1993,1316,1345,1086,1513,2078,1871,953,1757,1137,1097,2010,1047,2195,2166,1083,1949,1675,1864,1154,1888,1663,1356,1451,994,2007,2199,1246,2257,2824,2209,2175,1893,1852,962,2177,1010,795,1690,1310,1630,1693,1592,1135,1205,1395,1861,1092,2076,1414,1056,1953,1106,1052,1413,1299,1400,1488,2138,1462,1132,1809,2067,1834,989,2278,1168,1159,1960,2117,1527,1962,1784,1766,1624,1469,1821,1609,939,2024,1820,2861,1863,1164,1764,2132,1298,2187,1335,1443,1305,1344,1943,1568,2263,1446,2863,1375,1419,1869,1790,1577,975,1802,1303,2133,2064,1703,2090,1677,1588,1783,2051,979,2135,2103,1884,2123,1410,1758,1996,1442,1631,2120,2163,1559,1680,2134,1667,1851,2046,1080,1972,1607,1055,1539,1515,1500,2148,1131,1625,3179,1502,2021,1964,1969,1158,1642,1037,1580,1300,1554,1437,867,3299,1799,2094,1509,2758,1683,2075,2124,1612,1686,1854,1573,2055,1366,1698,1348,1599,1894,1273,1673,1475,961,2532,2215,1604,1781,1594,1872,1122,1426,1308,1485,1166,1637,1649,1444,2115,1687,1519,1150,1789,2065,1501,1578,1024,1576,1468,2146,2226,2071,1525,1543,2301,1617,980,2176,2068,1890,1039,1778,1314,2265,1551,1148,2229,1641,1843,1058,1628,1979,1650,1213,1653,1505,1803,1311,1572,1970,1982,1452,1813,2023,1846,1648,2167,2045,1140,1054,2189,2011,1965,1381,2044,1449,1435,1012,1212,1769,1457,1635,2180,1105,886,1332,2001,1405,2101,2092,1117,2173,1251,1706,1654,1439,1889,1991,1112,1315,1102,2106,1971,1453,2190,3124,2017,1506,2241,996,1174,1696,2170,1824,1059,1575,983,2264,2131,976,1242,1835,1376,1365,782,1160,1514,1338,1359,982,1806,1079,1445,1330,1826,2061,1701,1456,1483,1094,1948,1110,1490,1562,1440,2136,1041,2245,1640,2119,1814,1968,1765,1027,1593,1832,1672,2266,2162,1346,992,2717,2615,2085,1326,1020,1807,1377,1060,2084,2066,2086,1638,1017,1794,2216,1071,1885,1538,1754,2169,1051,1424,2511,1109,2261,1777,1980,1842,1352,2898,1859,1133,2058,2137,1441,2217,1035,1073,1019,2118,2091,1773,1389,1801,1412,2184,1240,1881,1537,1033,2186,1868,1499,1454,1584,1040,2219,2072,1597,1350,1812,1860,1643,2095,1858,1107,1339,1157,1465,1155,1526,1521,1528,1855,1541,1772,1669,959,2022,3296,1671,1983,1891,1797,1840,1805,1317,1517,1702,1598,971,1825,1759,2271,949,1795,999,1536,1626,2270,2077,1547,1204,1169,2179,1994,1874,1252,1171,2014,1085,2161,1353,1651,1438,1688,1322,755,1031,1670,1985,2158,2194,1865,1129,2100,2062,1367,1043,2240,2122,1088,1124,1243,1032,960,1516,1591,1988,2008,2079,1455,1652,1518,1819,2047,1542,2104,1487,1611,1391,2503,1636,1121,2174,1882,2262,1566,1343,1134,1323,1237,1785,1358,2041,1511,2019,1596,1756,1818,1695,2188,1600,771,2154,1360,2088,2081,952,1875,1618,1402,1399,1582,986,2239,2107,1214,1394,1341,1061,1347,1873,1111,2260,1082,2102,1392,1838,2277,1002,1491,1023,1817,2192,1172,2517,2269,1208,1351,1660,1633,1676,1333,1610,1034,1973,2013,2003,1892,1466,1997,1689,1634,1504,1699,1042,1545,1581,1482,1657,988,1340,964,1623,2197,990,2000,1396,2203,1379,973,1425,2056,1895,1390,1448,1981,2144,1116,2020,1796,1089,1380,1070,2862,1147,1656,2208,997,1605,1555,1068,2142,1000,1245,1211,2205,1423,956,1655,955,1114,1876,1304,1590,1244,1512,2002,1013,1207,1553,2070,2048,2201,1839,1093,2202,968,2052,1659,1318,993,2256,1939,1822,1139,1327,1450,2165,2039,1355,2207,1026,995,1420,1320,1867,1014,1125,2185,1421}'::integer[])))
  • Filter: (NOT ""IsDeleted"")
22. 0.124 0.483 ↑ 1.0 1,377 1

Hash (cost=220.13..220.13 rows=1,377 width=4) (actual time=0.483..0.483 rows=1,377 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 65kB
23. 0.359 0.359 ↑ 1.0 1,377 1

Seq Scan on ""LegalEntities"" l24 (cost=0.00..220.13 rows=1,377 width=4) (actual time=0.006..0.359 rows=1,377 loops=1)

  • Filter: ((NOT ""IsDeleted"") AND ""IsOnPortal"")
  • Rows Removed by Filter: 137
24. 1.549 7.115 ↓ 1.1 17,684 1

Hash (cost=990.80..990.80 rows=16,478 width=4) (actual time=7.115..7.115 rows=17,684 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 878kB
25. 2.040 5.566 ↓ 1.1 17,684 1

Hash Join (cost=318.69..990.80 rows=16,478 width=4) (actual time=0.798..5.566 rows=17,684 loops=1)

  • Hash Cond: (e56.""DivisionId"" = ""e.Division38"".""Id"")
26. 2.751 2.751 ↓ 1.0 17,967 1

Seq Scan on ""Employees"" e56 (cost=0.00..626.31 rows=17,420 width=8) (actual time=0.009..2.751 rows=17,967 loops=1)

  • Filter: ((NOT ""IsDeleted"") AND ""IsOnPortal"" AND (""DivisionId"" IS NOT NULL))
  • Rows Removed by Filter: 8064
27. 0.220 0.775 ↑ 1.0 2,345 1

Hash (cost=288.52..288.52 rows=2,414 width=4) (actual time=0.775..0.775 rows=2,345 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 115kB
28. 0.555 0.555 ↑ 1.0 2,345 1

Seq Scan on ""Divisions"" ""e.Division38"" (cost=0.00..288.52 rows=2,414 width=4) (actual time=0.004..0.555 rows=2,345 loops=1)

  • Filter: ""IsOnPortal"
  • Rows Removed by Filter: 168
29. 1.617 106.923 ↑ 1.6 8,325 1

Hash (cost=1,185.73..1,185.73 rows=13,016 width=4) (actual time=106.923..106.923 rows=8,325 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 421kB
30. 0.000 105.306 ↑ 1.6 8,325 1

Hash Left Join (cost=320.42..1,185.73 rows=13,016 width=4) (actual time=0.733..105.306 rows=8,325 loops=1)

  • Hash Cond: (e57.""DivisionId"" = ""e.Division39"".""Id"")
  • Filter: (SubPlan 1)
  • Rows Removed by Filter: 17706
31. 3.489 3.489 ↑ 1.0 26,031 1

Seq Scan on ""Employees"" e57 (cost=0.00..626.31 rows=26,031 width=8) (actual time=0.007..3.489 rows=26,031 loops=1)

  • Filter: (NOT ""IsDeleted"")
32. 0.209 0.552 ↑ 1.0 2,513 1

Hash (cost=288.52..288.52 rows=2,552 width=4) (actual time=0.552..0.552 rows=2,513 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 121kB
33. 0.343 0.343 ↑ 1.0 2,513 1

Seq Scan on ""Divisions"" ""e.Division39"" (cost=0.00..288.52 rows=2,552 width=4) (actual time=0.003..0.343 rows=2,513 loops=1)

34.          

SubPlan (for Hash Left Join)

35. 25.403 104.124 ↓ 0.0 0 26,031

Nested Loop (cost=0.98..17.06 rows=1 width=0) (actual time=0.004..0.004 rows=0 loops=26,031)

  • Join Filter: (a13.""Address_Id"" = ""a.AddressH2413"".""Id"")
36. 0.000 52.062 ↑ 1.0 1 26,031

Nested Loop (cost=0.70..16.73 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=26,031)

37. 26.031 26.031 ↑ 1.0 1 26,031

Index Only Scan using ""PK_dbo.DivisionAddresses"" on ""DivisionAddresses"" a13 (cost=0.28..8.30 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=26,031)

  • Index Cond: (""Division_Id"" = ""e.Division39"".""Id"")
  • Heap Fetches: 26659
38. 26.659 26.659 ↑ 1.0 1 26,659

Index Only Scan using ""PK_dbo.Addresses"" on ""Addresses"" ""a.Address13"" (cost=0.42..8.44 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=26,659)

  • Index Cond: (""Id"" = a13.""Address_Id"")
  • Heap Fetches: 26659
39. 26.659 26.659 ↓ 0.0 0 26,659

Index Scan using ""PK_dbo.AddressH24"" on ""AddressH24"" ""a.AddressH2413"" (cost=0.29..0.31 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=26,659)

  • Index Cond: ((""Id"" = ""a.Address13"".""Id"") AND (""Id"" IS NOT NULL))
  • Filter: (""SettlementId"" = 29219)
  • Rows Removed by Filter: 1
40. 3.944 8.627 ↑ 1.0 25,225 1

Hash (cost=723.93..723.93 rows=25,225 width=4) (actual time=8.627..8.627 rows=25,225 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1143kB
41. 4.683 4.683 ↑ 1.0 25,225 1

Seq Scan on ""Employees"" e58 (cost=0.00..723.93 rows=25,225 width=4) (actual time=0.008..4.683 rows=25,225 loops=1)

  • Filter: ((NOT ""IsDeleted"") AND ((""EmployeeTypeRefValue"")::text = ANY ('{DOCTOR,SPECIALIST,ASSISTANT}'::text[])))
  • Rows Removed by Filter: 806
42. 0.030 0.030 ↑ 1.0 1 10

Index Scan using ""IX_Doctors_PartyId"" on ""Doctors"" ""p.Doctors1"" (cost=0.29..8.30 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=10)

  • Index Cond: (""PartyId"" = p13.""Id"")
43. 0.012 0.012 ↓ 0.0 0 6

Index Scan using ""IX_ScienceDegrees_DoctorId"" on ""ScienceDegrees"" ""d.ScienceDegree1"" (cost=0.28..0.29 rows=1 width=6) (actual time=0.002..0.002 rows=0 loops=6)

  • Index Cond: (""p.Doctors1"".""Id"" = ""DoctorId"")
44. 0.018 0.018 ↑ 1.0 2 6

Index Scan using ""QualificationDoctors_IX_Doctor_Id"" on ""QualificationDoctors"" ""p.Doctors.DoctorQualifications"" (cost=0.29..8.50 rows=2 width=8) (actual time=0.003..0.003 rows=2 loops=6)

  • Index Cond: (""Doctor_Id"" = ""p.Doctors1"".""Id"")
45. 0.015 0.015 ↑ 1.0 1 15

Index Scan using ""PK_dbo.Qualifications"" on ""Qualifications"" ""d.Qualification"" (cost=0.29..0.42 rows=1 width=157) (actual time=0.001..0.001 rows=1 loops=15)

  • Index Cond: (""Id"" = ""p.Doctors.DoctorQualifications"".""Qualification_Id"")
46. 0.005 0.013 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=670) (actual time=0.013..0.013 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
47. 0.008 0.008 ↑ 1.0 8 1

Seq Scan on ""QualificationTypeRefs"" ""d.Qualification.QualificationTypeRef"" (cost=0.00..1.08 rows=8 width=670) (actual time=0.007..0.008 rows=8 loops=1)

Planning time : 4.382 ms
Execution time : 137.836 ms