Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sNQ1v-008WjI-H3 for pgsql-novice@arkaria.postgresql.org; Sat, 29 Jun 2024 04:49:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sNQ1t-0086qj-B9 for pgsql-novice@arkaria.postgresql.org; Sat, 29 Jun 2024 04:49:41 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sNQ1t-0086qb-1J for pgsql-novice@lists.postgresql.org; Sat, 29 Jun 2024 04:49:41 +0000 Received: from mail-wm1-x32a.google.com ([2a00:1450:4864:20::32a]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sNQ1m-00467g-3t for pgsql-novice@lists.postgresql.org; Sat, 29 Jun 2024 04:49:40 +0000 Received: by mail-wm1-x32a.google.com with SMTP id 5b1f17b1804b1-42563a9fa58so9540555e9.0 for ; Fri, 28 Jun 2024 21:49:33 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=cybertec-at.20230601.gappssmtp.com; s=20230601; t=1719636572; x=1720241372; darn=lists.postgresql.org; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id:from:to:cc :subject:date:message-id:reply-to; bh=LYFwgij9ELsTiG9U9Fw0oDHM/mV7px5mb3yt0Sxm4Og=; b=TflO0z5GbImmbzTvQOu1GSMsXhQRAIVj/v8E0caoIjde1Gp/6J6JNsTZL5CUJEd+pE f8Nqi97IO8RGO0IDyfJL4u9stMJMRKocrjzePMNZ3OieFm3FyQlbg8cYCOZz3uFqJPcy z/JByOekpjCm2+qJb9mDfhMGgHqyoIo/eHjLcXknap0in/bDSSJLYrc7gFkjL9r4MhUx a1QGXWSvyTYJhbBtj3NXeJXktUHUqKH5fawP8eHRaTRcEaQGSeNv0mlzm0eoIfROIixM Lsyhl9dq9M2uYNYeNyIl/qoyiYFfcYcCpU9hCEHKICwIbp//uCUOyPriGXkp2noA83VB dm7w== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719636572; x=1720241372; h=mime-version:user-agent:content-transfer-encoding:autocrypt :references:in-reply-to:date:to:from:subject:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=LYFwgij9ELsTiG9U9Fw0oDHM/mV7px5mb3yt0Sxm4Og=; b=cIbsorerP1jEzOxKOdqQFUavB+t6Qsb/yeahGVvFvE0uA81glutFTc8s7v98X09Nci io2mW7XSShnu9zYQ/wdwhu/T4fJrLFGuRZRRz65WKdViMAvHOwtklWSqy0G2UaCVLjgD 7Zx+ABT60Up1i5gf9WG52XA++V/B4sBmWPUww+A9LNuJ/PnTZFBBA+u5kQhifmdUcBZr k2B44ktV8vxaT+TOsD889sJZZ69NWyfdENbvraIv1TbSMzmTBRs/oCYkLyaD3m7W21Lb LA7VfRbqTULaS8RoJuOSSt/jx6JvkGPwIheQ0HMF7WpLAPfMk3rYC+wPmugHCYxoK8oC lXAg== X-Forwarded-Encrypted: i=1; AJvYcCVA3vOeO24Fo0RtuP/3bCZyO70cs6QsG7AYTbfhSaMNKumc90qphhbOZoXAkwh48wQ+BEO6LU/fZ0c86sJclyl8s1GyFvkxE4F/pjrJWlPX72k= X-Gm-Message-State: AOJu0YySoeKsACrrzlsGe/C4bTWOryzYmSNXt3Z53gUguWt5G/oRs5jx 4nvoXdPbVWQT9YmZc0KwJixcLAZ7MLueNhhko14F67B6e2hp59rkqArTiLLcYgI= X-Google-Smtp-Source: AGHT+IGzmqfN3THYVqx656onlp9cJOCq4UjehUVyOLuZNU/AhLdTfVqjkDdTNAXzueqEcvJnKgdVVQ== X-Received: by 2002:a05:600c:4f93:b0:424:a7f1:bac with SMTP id 5b1f17b1804b1-4257a02ce59mr577085e9.26.1719636572230; Fri, 28 Jun 2024 21:49:32 -0700 (PDT) Received: from localhost.localdomain ([41.66.99.10]) by smtp.gmail.com with ESMTPSA id ffacd0b85a97d-3675a0e141esm3895903f8f.58.2024.06.28.21.49.31 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Fri, 28 Jun 2024 21:49:31 -0700 (PDT) Message-ID: Subject: Re: Vacuum Analyze From: Laurenz Albe To: Michael Wallach , pgsql-novice@lists.postgresql.org Date: Sat, 29 Jun 2024 06:49:30 +0200 In-Reply-To: References: Autocrypt: addr=laurenz.albe@cybertec.at; prefer-encrypt=mutual; keydata=mQINBGGDwAQBEADgbWy5cKXQld3N2mF+DFyiNFbi2oBl2T+XgxpPF8wTRw2D/u4bBKXP0SYSE/lA86jIVNWWU0gf1KODIkVvgJm2w4vH2VBV1b7ddVViGl1Iu+9zaRnv9wulhnH42KefepXnoean6UT1EzLM0opF/Ik0j+40TxdRtobkBprkQUyHDXWlHc2ffPs3SipyFEP9AVLf7ejRC46CXWDnsqjOBSMEW8Z4HiK/8RrPZBsKLts8dJxKF4pygOdJb0CWk8k/X1jbcfdxo+zOLjOMvJcSJ2pFdJmQHU+JufB3rePziqQ2S9Ur6sccr9XnTC1GVBWN4Lf5VHq+vf+bFJjVwg+2hrySZnAVfcOrxoqFLErr7ug1zN2nM1kcpgA4VWn4gxlJtYNYYq+9WxX5dtvnNANlG3ZCrRKQzl8lxtzoF6Zo7LUhEqPaHDwn7Rvs+IdbOn41lF5UDTJGqmC4gS/bZydW2Fy3YWm4aSaN9fgFf8D+PVkrlKAZB7gBLz1TyHjbcRf85cYF+GKKrDld5SzMB/V60VX3oP/Eo8ikFpyWaqiz1f9X7MBot3/PjJkY+wDzp3nmb19QEcOBuQiSQ4xds2r0HewbuHTAR68u8jNNMGmpm2j4x+g09Jd/WQDjqlTBZ/jEltH41fYCCPWMfljXTOOXu2eLNGdfi7ETZogtwjM9oTtSPQARAQABtCdMYXVyZW56IEFsYmUgPGxhdXJlbnouYWxiZUBjeWJlcnRlYy5hdD6JAk4EEwEIADgWIQR0CqhbZGGABqoaSbdi8bhXA2EdmAUCYYPABAIbAwULCQgHAgYVCgkICwIEFgIDAQIeAQIXgAAKCRBi8bhXA2EdmM/6EADK232JCwmBzhlj8h7U9CjG6kx0JHP3uJGv+XfsHtHAlmY/RCwF1BHMEsRlk bT5UrLvJ2jb99bA9QARzhFaxzyn0F/BUKzuIjRGNs/n6d5dNUFA0kOt8sX+TacmC GEyjEBCrVCm4ranBiUyePn9NhHNWnaex7pJyqvMLLdwW9BEMJx0Fqo+DN8ukbXmYRsmhEtd3ue+x/luYmOmJnaGtzInaY5aOJYbW9XqoRIZkZvOCgbi1FfvNmoqWa+3oVxTOgw9RafjJDyW0lTHzKGjbGI5ofMU98l+/hKJFYJqWUF6VpFJY5YIcN/1lf4ZICMwDl+MPIVo/tpq8L10seJL28nLlvw3K+cI+TVW8IW/qL/LyVoDofI3USeOORuYmhpWRhik8JXX6xf3v6GrRilJIPWNFIJbxm1ZblQiQnOw3IOW7T+8nAmPin1HKqM3VrOrJQ2VtShsefNBibNAsr1oFaqcDBkn3yGG8i6CTW+FyO4PZ+/EwNxMVgktxbYdy5AT1/lpXr5tB+phhLIyVfiBvrWs5EThxYMQ/L8Y85c3GMsAy1l/x4h3jqySIYy3SCU9+jc5UVuNnXljbvkEzJ+NLWJ6C1rACFWrMszgPdh5tCrlRY9PpmYll4JbCgb8BtxEIUmR+xr50/ZElEK5iml7Q00KUekCcDt+36PsyGFTXBzNOrkCDQRhg8AEARAAzOZ2tLHlI4rrhG411h6cdCFjBZxuljaFCxFyHn3m6wbGLqwBUWC5k8UrRqjHMz88KcTSaNO7XGAmCqPdWd2SeflPZRnNTbjsVpw7mLdffsBm4JX7kki2Pvk5h0NtYeidXT1PSpc2ri4DutYXuT9uD8RAm1wUDCE5HQNUihT/WH6opt+hskHW21uHao0+y822tG0QQcGMqdQR5Vxdxj89wiEPdqW+HpU/oOZIhrf2E7prduAppxixjHy/o1rcnoznnJvc8D3+YgI9O0LrBMij89dM55pRGbLovTR1oGR3U74sX774+0xmSzeIKwZfiMUz7Atlvfk5SHOsRUFPN2Ux9kaXiiBibQpHFxt7b lDrT4wxdLJ/XCdbPPAyl+lZtOLsaHEEZvYNyTXwZc35dVf3R4/oz20HoG6s7ct8e1 AQygj43XAERzty9SkWgxs8+grp1PrGx6FHVSYRqBM8dS/ZR6yRVwOwJXPyaSSqfIF21DkE4j1y4n+ItSewPGoRp8K/yWCikt6qlkVkO2ASNIiX04fAbtzwVOaNn8ZMRNqyvLc1fED4sr49onE4cAIcBLjcC3KL+w9DUGRQCdziROj5H2Yl/sXGPdMciUHo/Uz2rggc+2th3bQiMhrHWSsBpUkDQp0yWewemstPpPgBL3h2fHKaX8B9oH5Qu/H1IgrOuX8AEQEAAYkCNgQYAQgAIBYhBHQKqFtkYYAGqhpJt2LxuFcDYR2YBQJhg8AEAhsMAAoJEGLxuFcDYR2YuPwQAMkpGtR80pQ1gVsONhdkqj0H2eU66efP/gO3CoyaoIcvrpKYj7C2HipVSmkt1gpByL0X4AMQ/vKuknUz3wd28Ba+G1dCfbVs/Xiusq+SmpUj5rTwmYqdSjWMuCo1R6oS5hdJMdUUJYGMT0QkVlm1KnW8jkmCTl9GzjDxOAsN9O6/6lPzaGFtk9XF+34Bry/N4HKiJkqpC4+UTd0AprPfzJ2jdT64e1F0+W88X8y1bTTgNrHwK4mDiLnlE4SKRuEm54lNhJz//ar86Or5BErzNpM6TL7lk44QS06hwsMrEdKIy8J/SYJPjfzR8tIUnKscclVpOgjKaBqC+0iFiVaRqAgfOlIEiezX6kMh5Q2FIUfqs46qWhhXjRrdKOEoStYAaikdLu5ZXr7vfb0ZaDh+ZwTQtbSMFolyOkecwI81MCdbMfT/1TqIGTOdAj5as9fAakk0jb2pXgUYQ8X1DVTR8ahSDVEaw9VTmWiSvTxvguVJ1Mb7gG4Gmh6aviDTJhfXtH4rPUNXhDLqrTH8JkJjyKROOMakIF68Hjse5vUfUxreBEOtb5r1Coa2Fe7ncJayaSE7ryrDbFqpZ 36UMAx4ulWMyqJajLNGY0DdG8qIsR5nxRhrnK/mrCidZ8F9/D3bWAl4rjtHlsztN59 +AnW5l0HsQcY9ntFL/zEBOaonjdJf Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable User-Agent: Evolution 3.50.4 (3.50.4-1.fc39) MIME-Version: 1.0 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On Fri, 2024-06-28 at 17:09 -0400, Michael Wallach wrote: > Having issue with DB where we see SQL get slow, not sure what is causing = but > queries that have been running sub second jump to 2-5 secs. Seems when we= manually > run VACUUM=C2=A0ANALYZE=C2=A0query perf immediately improves. To add to t= his we have a =E2=80=9Cjob=E2=80=9D > to trigger VACUUM ANALYZE to run every day but despite this, some acton a= gainst DB > results in performance regarding that is only fixed by again running VACU= UM ANALYZE. >=20 > What I=E2=80=99m trying to understand is what should I be looking at as b= efore/after in the > tables to determine what exactly VACUUM ANALYZE might be affecting to ide= ntify cause, > what is corrected in DB such that perf improves? The DB is being exercise= d by custom > apps via API so not sure what is happening that is causing DB to quickly = become > non-performant? You'd have to investigate that more closely. The key tool is EXPLAIN (ANALYZE, BUFFERS) SELECT ... Do that when the query is fast and then again later when the query is slow. The output tells you what exactly the database is doing to execute the quer= y, and it also tells you what parts are slow in the second case. Once you und= erstand the cause of the problem, you can usually address it in a much more precise= fashion. VACUUM (ANALYZE) on the whole database is a pretty big hammer. Perhaps all= that is necessary is to tune autovacuum to ANALYZE or VACUUM a certain table mor= e often. Yours, Laurenz Albe