Please help! Query jumps from 1s -> 4m

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hi,

Hoping someone can help with this performance issue that's been driving a few of us crazy :-) Any guidance greatly appreciated.

A description of what you are trying to achieve and what results you expect.:
 - I'd like to get an understanding of why the following query (presented in full, but there are specific parts that are confusing me) starts off taking ~second in duration but 'switches' to taking over 4 minutes.
 - we initially saw this behaviour for the exact same sql with a different index that resulted in an index scan. To try and fix the issue we've created an additional index with additional included fields so we now have Index Only Scans, but are still seeing the same problem.
 - execution plan is from auto_explain output when it took just over 4 minutes to execute. The time is shared ~equally between these two index-only scans.
 - There are no checkpoints occurring concurrently with this (based on "checkpoint starting" and "checkpoint complete" in logs)
 - bloat on the index is about 30%
 
 Segments of interest:
 1. ->  Index Only Scan using table1_typea_include_uniqueid_col16_idx on table1 table1alias1  (cost=0.56..17.25 rows=1 width=60) (actual time=110.539..123828.134 rows=67000 loops=1)
        Index Cond: (col20 = $2005)
        Filter: (((col3 = $2004) OR (col3 IS NULL)) AND ((col8)::text = ANY ((ARRAY[$1004, ..., $2003])::text[])))
        Rows Removed by Filter: 2662652
        Heap Fetches: 6940
        Buffers: shared hit=46619 read=42784 written=52

 2. ->  Index Only Scan using table1_typea_include_uniqueid_col16_idx on table1 table1alias2  (cost=0.56..17.23 rows=1 width=36) (actual time=142.855..122034.039 rows=67000 loops=1)
        Index Cond: (col20 = $1001)
        Filter: ((col8)::text = ANY ((ARRAY[$1, ..., $1000])::text[]))
        Rows Removed by Filter: 2662652
        Heap Fetches: 6891
        Buffers: shared hit=47062 read=42331 written=37
 
If I run the same queries now:
Index Only Scan using table1_typea_include_uniqueid_col16_idx on table1 table1alias1  (cost=0.56..2549.69 rows=69 width=36)
(actual time=1.017..1221.375 rows=67000 loops=1)
Heap Fetches: 24
Buffers: shared hit=2849 read=2483

buffers do look different - but still, reading 42k doesn't seem like it would cause a delay of 4m?

Actually, here's another example of segment 2 from logs.

    Index Only Scan using table1_typea_include_uniqueid_col16_idx on table1 table1alias2  (cost=0.56..17.23 rows=1 width=36) (actual time=36.559..120649.742 rows=65000 loops=1)
        Index Cond: (col20 = $1001)
        Filter: ((col8)::text = ANY ((ARRAY[$1, $1000]::text[]))
        Rows Removed by Filter: 2664256
        Heap Fetches: 6306
        Buffers: shared hit=87712 read=1507

One note: I've replaced table/column names (sorry, a requirement).

Full subquery execution plan (i've stripped out the view materialization from row 14 onwards but left the header in):
https://explain.depesz.com/s/vsdH

Full Sql:
SELECT
    subquery.id
FROM (
    SELECT
        table1alias1.id,
        table1alias1.uniqueid,
        table1alias1.col16 AS order_by
    FROM
        table1 AS table1alias1
    LEFT OUTER JOIN (
    SELECT
        inlinealias1.id,
        inlinealias1.uniqueid,
        inlinealias1.col4,
        inlinealias1.col5,
        inlinealias1.col6,
        inlinealias1.col7
    FROM (
        SELECT
            table2alias.id,
            table2alias.uniqueid,
            table2alias.col3,
            table2alias.col4,
            table2alias.col5,
            table2alias.col6,
            row_number() OVER (PARTITION BY table2alias.uniqueid ORDER BY table2alias.col13 DESC, table2alias.col3 DESC, table2alias.id DESC) AS rn
        FROM
            table2 AS table2alias
            JOIN ( SELECT DISTINCT
                    table1alias2.uniqueid
                FROM
                    table1 AS table1alias2
                WHERE (table1alias2.col8 IN ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18, $19, $20, $21, $22, $23, $24, $25, $26, $27, $28, $29, $30, $31, $32, $33, $34, $35, $36, $37, $38, $39, $40, $41, $42, $43, $44, $45, $46, $47, $48, $49, $50, $51, $52, $53, $54, $55, $56, $57, $58, $59, $60, $61, $62, $63, $64, $65, $66, $67, $68, $69, $70, $71, $72, $73, $74, $75, $76, $77, $78, $79, $80, $81, $82, $83, $84, $85, $86, $87, $88, $89, $90, $91, $92, $93, $94, $95, $96, $97, $98, $99, $100, $101, $102, $103, $104, $105, $106, $107, $108, $109, $110, $111, $112, $113, $114, $115, $116, $117, $118, $119, $120, $121, $122, $123, $124, $125, $126, $127, $128, $129, $130, $131, $132, $133, $134, $135, $136, $137, $138, $139, $140, $141, $142, $143, $144, $145, $146, $147, $148, $149, $150, $151, $152, $153, $154, $155, $156, $157, $158, $159, $160, $161, $162, $163, $164, $165, $166, $167, $168, $169, $170, $171, $172, $173, $174, $175, $176, $177, $178, $179, $180, $181, $182, $183, $184, $185, $186, $187, $188, $189, $190, $191, $192, $193, $194, $195, $196, $197, $198, $199, $200, $201, $202, $203, $204, $205, $206, $207, $208, $209, $210, $211, $212, $213, $214, $215, $216, $217, $218, $219, $220, $221, $222, $223, $224, $225, $226, $227, $228, $229, $230, $231, $232, $233, $234, $235, $236, $237, $238, $239, $240, $241, $242, $243, $244, $245, $246, $247, $248, $249, $250, $251, $252, $253, $254, $255, $256, $257, $258, $259, $260, $261, $262, $263, $264, $265, $266, $267, $268, $269, $270, $271, $272, $273, $274, $275, $276, $277, $278, $279, $280, $281, $282, $283, $284, $285, $286, $287, $288, $289, $290, $291, $292, $293, $294, $295, $296, $297, $298, $299, $300, $301, $302, $303, $304, $305, $306, $307, $308, $309, $310, $311, $312, $313, $314, $315, $316, $317, $318, $319, $320, $321, $322, $323, $324, $325, $326, $327, $328, $329, $330, $331, $332, $333, $334, $335, $336, $337, $338, $339, $340, $341, $342, $343, $344, $345, $346, $347, $348, $349, $350, $351, $352, $353, $354, $355, $356, $357, $358, $359, $360, $361, $362, $363, $364, $365, $366, $367, $368, $369, $370, $371, $372, $373, $374, $375, $376, $377, $378, $379, $380, $381, $382, $383, $384, $385, $386, $387, $388, $389, $390, $391, $392, $393, $394, $395, $396, $397, $398, $399, $400, $401, $402, $403, $404, $405, $406, $407, $408, $409, $410, $411, $412, $413, $414, $415, $416, $417, $418, $419, $420, $421, $422, $423, $424, $425, $426, $427, $428, $429, $430, $431, $432, $433, $434, $435, $436, $437, $438, $439, $440, $441, $442, $443, $444, $445, $446, $447, $448, $449, $450, $451, $452, $453, $454, $455, $456, $457, $458, $459, $460, $461, $462, $463, $464, $465, $466, $467, $468, $469, $470, $471, $472, $473, $474, $475, $476, $477, $478, $479, $480, $481, $482, $483, $484, $485, $486, $487, $488, $489, $490, $491, $492, $493, $494, $495, $496, $497, $498, $499, $500, $501, $502, $503, $504, $505, $506, $507, $508, $509, $510, $511, $512, $513, $514, $515, $516, $517, $518, $519, $520, $521, $522, $523, $524, $525, $526, $527, $528, $529, $530, $531, $532, $533, $534, $535, $536, $537, $538, $539, $540, $541, $542, $543, $544, $545, $546, $547, $548, $549, $550, $551, $552, $553, $554, $555, $556, $557, $558, $559, $560, $561, $562, $563, $564, $565, $566, $567, $568, $569, $570, $571, $572, $573, $574, $575, $576, $577, $578, $579, $580, $581, $582, $583, $584, $585, $586, $587, $588, $589, $590, $591, $592, $593, $594, $595, $596, $597, $598, $599, $600, $601, $602, $603, $604, $605, $606, $607, $608, $609, $610, $611, $612, $613, $614, $615, $616, $617, $618, $619, $620, $621, $622, $623, $624, $625, $626, $627, $628, $629, $630, $631, $632, $633, $634, $635, $636, $637, $638, $639, $640, $641, $642, $643, $644, $645, $646, $647, $648, $649, $650, $651, $652, $653, $654, $655, $656, $657, $658, $659, $660, $661, $662, $663, $664, $665, $666, $667, $668, $669, $670, $671, $672, $673, $674, $675, $676, $677, $678, $679, $680, $681, $682, $683, $684, $685, $686, $687, $688, $689, $690, $691, $692, $693, $694, $695, $696, $697, $698, $699, $700, $701, $702, $703, $704, $705, $706, $707, $708, $709, $710, $711, $712, $713, $714, $715, $716, $717, $718, $719, $720, $721, $722, $723, $724, $725, $726, $727, $728, $729, $730, $731, $732, $733, $734, $735, $736, $737, $738, $739, $740, $741, $742, $743, $744, $745, $746, $747, $748, $749, $750, $751, $752, $753, $754, $755, $756, $757, $758, $759, $760, $761, $762, $763, $764, $765, $766, $767, $768, $769, $770, $771, $772, $773, $774, $775, $776, $777, $778, $779, $780, $781, $782, $783, $784, $785, $786, $787, $788, $789, $790, $791, $792, $793, $794, $795, $796, $797, $798, $799, $800, $801, $802, $803, $804, $805, $806, $807, $808, $809, $810, $811, $812, $813, $814, $815, $816, $817, $818, $819, $820, $821, $822, $823, $824, $825, $826, $827, $828, $829, $830, $831, $832, $833, $834, $835, $836, $837, $838, $839, $840, $841, $842, $843, $844, $845, $846, $847, $848, $849, $850, $851, $852, $853, $854, $855, $856, $857, $858, $859, $860, $861, $862, $863, $864, $865, $866, $867, $868, $869, $870, $871, $872, $873, $874, $875, $876, $877, $878, $879, $880, $881, $882, $883, $884, $885, $886, $887, $888, $889, $890, $891, $892, $893, $894, $895, $896, $897, $898, $899, $900, $901, $902, $903, $904, $905, $906, $907, $908, $909, $910, $911, $912, $913, $914, $915, $916, $917, $918, $919, $920, $921, $922, $923, $924, $925, $926, $927, $928, $929, $930, $931, $932, $933, $934, $935, $936, $937, $938, $939, $940, $941, $942, $943, $944, $945, $946, $947, $948, $949, $950, $951, $952, $953, $954, $955, $956, $957, $958, $959, $960, $961, $962, $963, $964, $965, $966, $967, $968, $969, $970, $971, $972, $973, $974, $975, $976, $977, $978, $979, $980, $981, $982, $983, $984, $985, $986, $987, $988, $989, $990, $991, $992, $993, $994, $995, $996, $997, $998, $999, $1000)
                    AND (table1alias2.datatype IN (CAST('TypeA' AS datatype_enum)))
                    AND table1alias2.col20 IN ($1001))) AS candidateUniqueId ON table2alias.uniqueid = candidateUniqueId.uniqueid) AS inlinealias1
    WHERE
        inlinealias1.rn = $1002) AS inlinealias2 ON table1alias1.uniqueid = inlinealias2.uniqueid
WHERE (EXISTS (
        SELECT
            1 AS one
        FROM
            view1
        WHERE (col8 = $1003
            AND table1alias1.col20 = col2))
    AND table1alias1.col8 IN ($1004, $1005, $1006, $1007, $1008, $1009, $1010, $1011, $1012, $1013, $1014, $1015, $1016, $1017, $1018, $1019, $1020, $1021, $1022, $1023, $1024, $1025, $1026, $1027, $1028, $1029, $1030, $1031, $1032, $1033, $1034, $1035, $1036, $1037, $1038, $1039, $1040, $1041, $1042, $1043, $1044, $1045, $1046, $1047, $1048, $1049, $1050, $1051, $1052, $1053, $1054, $1055, $1056, $1057, $1058, $1059, $1060, $1061, $1062, $1063, $1064, $1065, $1066, $1067, $1068, $1069, $1070, $1071, $1072, $1073, $1074, $1075, $1076, $1077, $1078, $1079, $1080, $1081, $1082, $1083, $1084, $1085, $1086, $1087, $1088, $1089, $1090, $1091, $1092, $1093, $1094, $1095, $1096, $1097, $1098, $1099, $1100, $1101, $1102, $1103, $1104, $1105, $1106, $1107, $1108, $1109, $1110, $1111, $1112, $1113, $1114, $1115, $1116, $1117, $1118, $1119, $1120, $1121, $1122, $1123, $1124, $1125, $1126, $1127, $1128, $1129, $1130, $1131, $1132, $1133, $1134, $1135, $1136, $1137, $1138, $1139, $1140, $1141, $1142, $1143, $1144, $1145, $1146, $1147, $1148, $1149, $1150, $1151, $1152, $1153, $1154, $1155, $1156, $1157, $1158, $1159, $1160, $1161, $1162, $1163, $1164, $1165, $1166, $1167, $1168, $1169, $1170, $1171, $1172, $1173, $1174, $1175, $1176, $1177, $1178, $1179, $1180, $1181, $1182, $1183, $1184, $1185, $1186, $1187, $1188, $1189, $1190, $1191, $1192, $1193, $1194, $1195, $1196, $1197, $1198, $1199, $1200, $1201, $1202, $1203, $1204, $1205, $1206, $1207, $1208, $1209, $1210, $1211, $1212, $1213, $1214, $1215, $1216, $1217, $1218, $1219, $1220, $1221, $1222, $1223, $1224, $1225, $1226, $1227, $1228, $1229, $1230, $1231, $1232, $1233, $1234, $1235, $1236, $1237, $1238, $1239, $1240, $1241, $1242, $1243, $1244, $1245, $1246, $1247, $1248, $1249, $1250, $1251, $1252, $1253, $1254, $1255, $1256, $1257, $1258, $1259, $1260, $1261, $1262, $1263, $1264, $1265, $1266, $1267, $1268, $1269, $1270, $1271, $1272, $1273, $1274, $1275, $1276, $1277, $1278, $1279, $1280, $1281, $1282, $1283, $1284, $1285, $1286, $1287, $1288, $1289, $1290, $1291, $1292, $1293, $1294, $1295, $1296, $1297, $1298, $1299, $1300, $1301, $1302, $1303, $1304, $1305, $1306, $1307, $1308, $1309, $1310, $1311, $1312, $1313, $1314, $1315, $1316, $1317, $1318, $1319, $1320, $1321, $1322, $1323, $1324, $1325, $1326, $1327, $1328, $1329, $1330, $1331, $1332, $1333, $1334, $1335, $1336, $1337, $1338, $1339, $1340, $1341, $1342, $1343, $1344, $1345, $1346, $1347, $1348, $1349, $1350, $1351, $1352, $1353, $1354, $1355, $1356, $1357, $1358, $1359, $1360, $1361, $1362, $1363, $1364, $1365, $1366, $1367, $1368, $1369, $1370, $1371, $1372, $1373, $1374, $1375, $1376, $1377, $1378, $1379, $1380, $1381, $1382, $1383, $1384, $1385, $1386, $1387, $1388, $1389, $1390, $1391, $1392, $1393, $1394, $1395, $1396, $1397, $1398, $1399, $1400, $1401, $1402, $1403, $1404, $1405, $1406, $1407, $1408, $1409, $1410, $1411, $1412, $1413, $1414, $1415, $1416, $1417, $1418, $1419, $1420, $1421, $1422, $1423, $1424, $1425, $1426, $1427, $1428, $1429, $1430, $1431, $1432, $1433, $1434, $1435, $1436, $1437, $1438, $1439, $1440, $1441, $1442, $1443, $1444, $1445, $1446, $1447, $1448, $1449, $1450, $1451, $1452, $1453, $1454, $1455, $1456, $1457, $1458, $1459, $1460, $1461, $1462, $1463, $1464, $1465, $1466, $1467, $1468, $1469, $1470, $1471, $1472, $1473, $1474, $1475, $1476, $1477, $1478, $1479, $1480, $1481, $1482, $1483, $1484, $1485, $1486, $1487, $1488, $1489, $1490, $1491, $1492, $1493, $1494, $1495, $1496, $1497, $1498, $1499, $1500, $1501, $1502, $1503, $1504, $1505, $1506, $1507, $1508, $1509, $1510, $1511, $1512, $1513, $1514, $1515, $1516, $1517, $1518, $1519, $1520, $1521, $1522, $1523, $1524, $1525, $1526, $1527, $1528, $1529, $1530, $1531, $1532, $1533, $1534, $1535, $1536, $1537, $1538, $1539, $1540, $1541, $1542, $1543, $1544, $1545, $1546, $1547, $1548, $1549, $1550, $1551, $1552, $1553, $1554, $1555, $1556, $1557, $1558, $1559, $1560, $1561, $1562, $1563, $1564, $1565, $1566, $1567, $1568, $1569, $1570, $1571, $1572, $1573, $1574, $1575, $1576, $1577, $1578, $1579, $1580, $1581, $1582, $1583, $1584, $1585, $1586, $1587, $1588, $1589, $1590, $1591, $1592, $1593, $1594, $1595, $1596, $1597, $1598, $1599, $1600, $1601, $1602, $1603, $1604, $1605, $1606, $1607, $1608, $1609, $1610, $1611, $1612, $1613, $1614, $1615, $1616, $1617, $1618, $1619, $1620, $1621, $1622, $1623, $1624, $1625, $1626, $1627, $1628, $1629, $1630, $1631, $1632, $1633, $1634, $1635, $1636, $1637, $1638, $1639, $1640, $1641, $1642, $1643, $1644, $1645, $1646, $1647, $1648, $1649, $1650, $1651, $1652, $1653, $1654, $1655, $1656, $1657, $1658, $1659, $1660, $1661, $1662, $1663, $1664, $1665, $1666, $1667, $1668, $1669, $1670, $1671, $1672, $1673, $1674, $1675, $1676, $1677, $1678, $1679, $1680, $1681, $1682, $1683, $1684, $1685, $1686, $1687, $1688, $1689, $1690, $1691, $1692, $1693, $1694, $1695, $1696, $1697, $1698, $1699, $1700, $1701, $1702, $1703, $1704, $1705, $1706, $1707, $1708, $1709, $1710, $1711, $1712, $1713, $1714, $1715, $1716, $1717, $1718, $1719, $1720, $1721, $1722, $1723, $1724, $1725, $1726, $1727, $1728, $1729, $1730, $1731, $1732, $1733, $1734, $1735, $1736, $1737, $1738, $1739, $1740, $1741, $1742, $1743, $1744, $1745, $1746, $1747, $1748, $1749, $1750, $1751, $1752, $1753, $1754, $1755, $1756, $1757, $1758, $1759, $1760, $1761, $1762, $1763, $1764, $1765, $1766, $1767, $1768, $1769, $1770, $1771, $1772, $1773, $1774, $1775, $1776, $1777, $1778, $1779, $1780, $1781, $1782, $1783, $1784, $1785, $1786, $1787, $1788, $1789, $1790, $1791, $1792, $1793, $1794, $1795, $1796, $1797, $1798, $1799, $1800, $1801, $1802, $1803, $1804, $1805, $1806, $1807, $1808, $1809, $1810, $1811, $1812, $1813, $1814, $1815, $1816, $1817, $1818, $1819, $1820, $1821, $1822, $1823, $1824, $1825, $1826, $1827, $1828, $1829, $1830, $1831, $1832, $1833, $1834, $1835, $1836, $1837, $1838, $1839, $1840, $1841, $1842, $1843, $1844, $1845, $1846, $1847, $1848, $1849, $1850, $1851, $1852, $1853, $1854, $1855, $1856, $1857, $1858, $1859, $1860, $1861, $1862, $1863, $1864, $1865, $1866, $1867, $1868, $1869, $1870, $1871, $1872, $1873, $1874, $1875, $1876, $1877, $1878, $1879, $1880, $1881, $1882, $1883, $1884, $1885, $1886, $1887, $1888, $1889, $1890, $1891, $1892, $1893, $1894, $1895, $1896, $1897, $1898, $1899, $1900, $1901, $1902, $1903, $1904, $1905, $1906, $1907, $1908, $1909, $1910, $1911, $1912, $1913, $1914, $1915, $1916, $1917, $1918, $1919, $1920, $1921, $1922, $1923, $1924, $1925, $1926, $1927, $1928, $1929, $1930, $1931, $1932, $1933, $1934, $1935, $1936, $1937, $1938, $1939, $1940, $1941, $1942, $1943, $1944, $1945, $1946, $1947, $1948, $1949, $1950, $1951, $1952, $1953, $1954, $1955, $1956, $1957, $1958, $1959, $1960, $1961, $1962, $1963, $1964, $1965, $1966, $1967, $1968, $1969, $1970, $1971, $1972, $1973, $1974, $1975, $1976, $1977, $1978, $1979, $1980, $1981, $1982, $1983, $1984, $1985, $1986, $1987, $1988, $1989, $1990, $1991, $1992, $1993, $1994, $1995, $1996, $1997, $1998, $1999, $2000, $2001, $2002, $2003)
    AND (table1alias1.col3 = $2004
        OR table1alias1.col3 IS NULL)
    AND (table1alias1.datatype IN (CAST('TypeA' AS datatype_enum)))
    AND table1alias1.col20 IN ($2005))
ORDER BY
    table1alias1.col16 ASC) AS subquery
ORDER BY
    subquery.order_by ASC


Tables:
\d table1
                                                  Table "table1"
              Column               |            Type             | Collation | Nullable |             Default            
-----------------------------------+-----------------------------+-----------+----------+---------------------------------
 id                                | bigint                      |           | not null |
 col2                              | bigint                      |           |          |
 col3                              | boolean                     |           |          |
 col4                              | timestamp without time zone |           |          |
 col5                              | timestamp without time zone |           |          |
 col6                              | timestamp without time zone |           |          |
 col7                              | timestamp without time zone |           |          |
 col8                              | character varying(1000)     |           |          |
 col9                              | character varying(1000)     |           |          |
 col10                             | character varying(1000)     |           |          |
 col11                             | character varying(1000)     |           |          |
 col12                             | character varying(1000)     |           |          |
 col13                             | character varying(1000)     |           |          |
 col14                             | character varying(1000)     |           |          |
 col15                             | character varying(1000)     |           |          |
 col16                             | bigint                      |           |          |
 col17                             | bigint                      |           |          |
 col18                             | bigint                      |           |          |
 col19                             | bigint                      |           |          |
 col20                             | bigint                      |           |          |
 col21                             | character varying(255)      |           |          |
 uniqueid                          | character varying(255)      |           |          |
 col23                             | timestamp without time zone |           |          |
 col24                             | boolean                     |           |          |
 col25                             | boolean                     |           |          |
 col26                             | character varying(255)      |           |          |
 col27                             | timestamp without time zone |           |          |
 col28                             | timestamp without time zone |           |          |
 col29                             | bigint                      |           |          |
 col30                             | integer                     |           | not null | 0
 col31                             | character varying(255)      |           |          |
 col32                             | boolean                     |           |          |
 col33                             | boolean                     |           |          |
 col34                             | boolean                     |           |          |
 col35                             | boolean                     |           |          |
 col36                             | character varying(1000)     |           |          |
 col37                             | character varying(1000)     |           |          |
 col38                             | boolean                     |           |          |
 col39                             | character varying(1000)     |           |          |
 col40                             | character varying(1000)     |           |          |
 col41                             | character varying(1000)     |           |          |
 col42                             | character varying(255)      |           |          |
 col43                             | character varying(1000)     |           |          |
 col44                             | other_enum                  |           | not null |
 datatype                          | datatype_enum               |           | not null |
 col46                             | bigint                      |           |          |
 col47                             | text                        |           |          |
 col48                             | bytea                       |           | not null |
 col49                             | bytea                       |           |          |
 col50                             | bigint                      |           |          |
 col51                             | bigint                      |           |          |
 col52                             | bigint                      |           |          |
 col53                             | bigint                      |           |          |
 col54                             | bigint                      |           |          |
Indexes:
    "table1_pkey" PRIMARY KEY, btree (id)
    "table1_unique_col47_for_col46" UNIQUE CONSTRAINT, btree (col47, col46)
    "table1_col20_datatype_idx" btree (col20, datatype)
    "table1_col2_datatype_col20_idx" btree (col2, datatype, col20)
    "table1_col42_idx" btree (col42)
    "table1_col28_idx" btree (col28)
    "table1_col52_idx" btree (col52)
    "table1_col50_idx" btree (col50)
    "table1_col12_idx" btree (col12)
    "table1_col37_idx" btree (col37) WHERE col37 IS NOT NULL
    "table1_col40_notnull_idx" btree (col40) WHERE col40 IS NOT NULL
    "table1_typea_idx" btree (col20, (col8::text)) WHERE datatype = 'TypeA'::table1_type
    "table1_typea_include_uniqueid_col16_idx" btree (col20, col8, deleted) INCLUDE (uniqueid, col16, id) WHERE datatype = 'TypeA'::table1_type
    "table1_typea_idx" btree (col20, (col8::text)) WHERE datatype = 'TypeA'::table1_type INVALID
    "table1_uniqueid_idx" btree (uniqueid)
Check constraints:
    "table1_col32_not_null" CHECK (col32 IS NOT NULL)
    "table1_col34_not_null" CHECK (col34 IS NOT NULL)
    "table1_col33_not_null" CHECK (col33 IS NOT NULL)
    "table1_col35_not_null" CHECK (col35 IS NOT NULL) NOT VALID
    "col49_or_col46" CHECK (col49 IS NOT NULL OR col46 IS NOT NULL) NOT VALID
Foreign-key constraints:
    "fk_table1_col20" FOREIGN KEY (col20) REFERENCES constainttable4(id)
    "fk_table1_col29" FOREIGN KEY (col29) REFERENCES constainttable5(id)
    "table1_col46_fkey" FOREIGN KEY (col46) REFERENCES constainttable6(id)
Referenced by:
    TABLE "referencetable2" CONSTRAINT "fk_table1_id" FOREIGN KEY (table1_id) REFERENCES table1(id)
    TABLE "referencetable3" CONSTRAINT "referencetable3_table1_id_fkey" FOREIGN KEY (table1_id) REFERENCES table1(id)
    TABLE "referencetable4" CONSTRAINT "referencetable4_table1_fkey" FOREIGN KEY (table1_id) REFERENCES table1(id)
Publications:
    "puba"


\d view1
                                View "view1"
                 Column                  |          Type          | Collation | Nullable | Default
-----------------------------------------+------------------------+-----------+----------+---------
 col1                                    | text                   |           |          |
 col2                                    | bigint                 |           |          |
 col3                                    | bytea                  |           |          |
 col4                                    | integer                |           |          |
 col5                                    | character varying(255) |           |          |
 col6                                    | bytea                  |           |          |
 col7                                    | character varying(255) |           |          |
 col8                                    | bigint                 |           |          |
 col9                                    | bigint                 |           |          |
 col10                                   | bytea                  |           |          |
 col11                                   | integer                |           |          |
 col12                                   | character varying      |           |          |
 col13                                   | bytea                  |           |          |
 col14                                   | character varying      |           |          |



 \d table2
                                              Table "table2"
        Column         |            Type             | Collation | Nullable |                  Default                  
-----------------------+-----------------------------+-----------+----------+-------------------------------------------
 id                    | bigint                      |           | not null | nextval('table2_id_seq'::regclass)
 uniqueid              | character varying(255)      |           | not null |
 col3                  | timestamp without time zone |           | not null |
 col4                  | boolean                     |           | not null |
 col5                  | boolean                     |           | not null |
 col6                  | boolean                     |           | not null |
 col7                  | boolean                     |           | not null |
 col8                  | bigint                      |           |          |
 col9                  | bigint                      |           |          |
 col10                 | bigint                      |           |          |
 col11                 | character varying(255)      |           |          |
 col12                 | character varying(255)      |           | not null |
 col13                 | boolean                     |           | not null | true
Indexes:
    "id" PRIMARY KEY, btree (id)
    "idx_table2_uniqueid" btree (uniqueid)
Foreign-key constraints:
    "fk_table2_source_constainttable1_id" FOREIGN KEY (source_constainttable1_id) REFERENCES constainttable1(id)
    "fk_table2_source_constainttable2_id" FOREIGN KEY (source_constainttable2_id) REFERENCES constainttable2(id)
    "fk_table2_source_constainttable3_id" FOREIGN KEY (source_constainttable3_id) REFERENCES constainttable3(id)
Referenced by:
    TABLE "referencetable1" CONSTRAINT "fk_referencetable1_table2_id" FOREIGN KEY (table2_id) REFERENCES table2(id)
    TABLE "referencetable1" CONSTRAINT "fk_referencetable1_table2_id_old" FOREIGN KEY (table2_id_old) REFERENCES table2(id)


\d app.table1_typea_include_uniqueid_col16_idx
Index "table1_typea_include_uniqueid_col16_idx"
   Column   |          Type           | Key? | Definition
------------+-------------------------+------+------------
 col20      | bigint                  | yes  | account_id
 col8       | character varying(1000) | yes  | string01
 col3       | boolean                 | yes  | deleted
 col26      | character varying(255)  | no   | uniqueid
 col16      | bigint                  | no   | long01
 id         | bigint                  | no   | id
btree, for table "table1", predicate (datatype = 'TypeA'::table1_type)



PostgreSQL version number you are running:
SELECT version();
                                                              version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 11.6 (Ubuntu 11.6-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit
(1 row)


How you installed PostgreSQL:
Official apt repo

Changes made to the settings in the postgresql.conf file:  see Server Configuration for a quick way to list them all.
SELECT name, current_setting(name), SOURCE
  FROM pg_settings
  WHERE SOURCE NOT IN ('default', 'override');
              name               |            current_setting            |        source        
---------------------------------+---------------------------------------+----------------------
 application_name                | psql                                  | client
 archive_command                 | true                                  | configuration file
 archive_mode                    | on                                    | configuration file
 auto_explain.log_analyze        | on                                    | configuration file
 auto_explain.log_buffers        | on                                    | configuration file
 auto_explain.log_min_duration   | 10s                                   | configuration file
 auto_explain.log_timing         | on                                    | configuration file
 autovacuum                      | on                                    | configuration file
 checkpoint_timeout              | 30min                                 | configuration file
 client_encoding                 | UTF8                                  | client
 DateStyle                       | ISO, MDY                              | configuration file
 default_text_search_config      | pg_catalog.english                    | configuration file
 dynamic_shared_memory_type      | posix                                 | configuration file
 effective_cache_size            | 8GB                                   | configuration file
 effective_io_concurrency        | 100                                   | configuration file
 external_pid_file               | /var/run/postgresql/11-main.pid       | command line
 hot_standby                     | on                                    | configuration file
 lc_messages                     | en_US.utf8                            | configuration file
 lc_monetary                     | en_US.utf8                            | configuration file
 lc_numeric                      | en_US.utf8                            | configuration file
 lc_time                         | en_US.utf8                            | configuration file
 listen_addresses                | *                                     | configuration file
 log_autovacuum_min_duration     | 0                                     | configuration file
 log_checkpoints                 | on                                    | configuration file
 log_connections                 | on                                    | configuration file
 log_destination                 | syslog                                | configuration file
 log_directory                   | pg_log                                | configuration file
 log_disconnections              | on                                    | configuration file
 log_filename                    | postgresql-%a.log                     | configuration file

Operating system and version:
5.0.0-1029-gcp #30~18.04.1-Ubuntu SMP Mon Jan 13 05:40:56 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

What program you're using to connect to PostgreSQL:
Java JDBC 4.2 (JRE 8+) driver for PostgreSQL database
 
Is there anything relevant or unusual in the PostgreSQL server logs?:
nothing in the logs, but I've done a bunch of pg_locks/stat_activity dumps whilst the query is running. pg_locks/activity show:
 - wait_event_type/wait_event NULL each time for this query
 - granted = true for all modes for this query
 - an autovacuum was running, but for an unrelated table.

Extra perf details:
CPU: 16 vCPUs on GCP (Intel Xeon E5 v4)
RAM: 60GB
Disk: 3TB persistent SSD on GCP

sudo time dd if=/dev/sdc of=/dev/null bs=1M count=1k skip=$((128*RANDOM/32)):
1024+0 records in
1024+0 records out
1073741824 bytes (1.1 GB, 1.0 GiB) copied, 0.855067 s, 1.3 GB/s
0.00user 0.45system 0:00.85elapsed 53%CPU (0avgtext+0avgdata 3236maxresident)k
2097816inputs+0outputs (1major+348minor)pagefaults 0swaps

Estimated performance (GCP quoted)
Operation type Read Write
Sustained random IOPS limit 25,000.00 25,000.00
Sustained throughput limit (MB/s) 1,200.00 800.00



[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux