Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1omAs1-0002YC-SR for pgsql-general@arkaria.postgresql.org; Sat, 22 Oct 2022 09:32:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1omArz-0003Wo-6W for pgsql-general@arkaria.postgresql.org; Sat, 22 Oct 2022 09:32:43 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1omAry-0003Wf-Ny for pgsql-general@lists.postgresql.org; Sat, 22 Oct 2022 09:32:42 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1omArt-0004Gr-CN for pgsql-general@lists.postgresql.org; Sat, 22 Oct 2022 09:32:41 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id AFAEA7F79; Sat, 22 Oct 2022 11:32:32 +0200 (CEST) Date: Sat, 22 Oct 2022 11:32:32 +0200 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: Explain returns different number of rows Message-ID: <20221022093232.mrzvpsosul7sslyw@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <6B906119-82AD-49FC-A47D-8652FD03D936@thebuild.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="hvbjw34mrhnklcex" Content-Disposition: inline In-Reply-To: <6B906119-82AD-49FC-A47D-8652FD03D936@thebuild.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --hvbjw34mrhnklcex Content-Type: text/plain; charset=utf-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2022-10-20 09:56:23 -0700, Christophe Pettus wrote: > On Oct 20, 2022, at 09:52, Vince McMahon = wrote: > > The number of rows are different.=20 >=20 > This isn't unexpected. EXPLAIN does not actually run the query and > determine how many rows are returned; it calculates an estimate based > on the current system statistics, which vary constantly depending on > activity in the database. EXPLAIN ANALYZE (which is what he did) does run the query and return the actual number of rows: #v+ wdsah=3D> explain (analyze, buffers) select * from facttable_eurostat_comex= t_cpa2_1 ; =E2=95=94=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=97 =E2=95=91 = QUERY PLAN = =E2=95=91 =E2=95=9F=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80= =E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2= =94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94= =80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=95=A2 =E2=95=91 Seq Scan on facttable_eurostat_comext_cpa2_1 (cost=3D0.00..10057= 41.32 rows=3D39633432 width=3D85) (actual time=3D0.396..6541.701 rows=3D396= 33591 loops=3D1) =E2=95=91 =E2=95=91 Buffers: shared read=3D609407 = = =E2=95=91 =E2=95=91 Planning Time: 1.650 ms = = =E2=95=91 =E2=95=91 Execution Time: 7913.027 ms = = =E2=95=91 =E2=95=9A=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90= =E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2= =95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95= =90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=90=E2=95=9D (4 rows) #v- The first tuple (cost=3D0.00..1005741.32 rows=3D39633432 width=3D85) is an estimate used to plan the query. But the second one (actual time=3D0.396..6541.701 rows=3D39633591 loops=3D1) contains measurements from actually running the query. I think it's possible that the rows estimate in the first tuple changes without any actual data change (although the only reason I can think of right now would be an ANALYZE (in another session or by autovacuum)). But the actual rows definitely shouldn't change. hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --hvbjw34mrhnklcex Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmNTuKsACgkQ8g5IURL+ KF0pfw/+JeZgxNxyBzBa5yjpM2iFEMJVSvgNB32l7Jh2Pi5gfSEjAxo+h5SHqUGm UOLkw2aW9tyx3gEW7JWVa+7SH6nkevGEAf43jHRXmKRBmuy5nukw0XMpsJWJuxPE 2AqlctuxyfG4/cWPZjvQuWBcbP6xQdFkCREsFucreDaudj247Zs8zLEgupPgvLes 4KrYPsrafwZUJ+iFZrSViVLKzgTin0tU2xUPw8j4JKzmKR8/KqiIt+qc3wczcPOK NLDhQL1vDsiIh+ZSxz+EX6mavM748EcDXkcPIblz7fuiOqVcfO9nuY9t+F08TmNA 8LMXENFIxY8wI9WfHEJrjwa+dghwP37oGEcrNWLv9WSVe2WTBY014079mWPlqKei aXJiDULZwUr2Qqi0lRKSZV/RMdDpKyfusPJzSY8IloiGeU565vqSgZu3ry+4h5Hq uUjuIEjruKZejAfDT9ydvMR8GPoow2z6GH0QX+Q+WrS5tbe2IKryxdQmw4FqSMvb zhL/+9YclmhLeDeQFYJ1sF3fCkyRJNXzzjeotkmjorxyMuQxco1Id69NST8DXL+L oBTBNnWX0X115axBh/m1+1B6u6ZGZCcoeJYgqr/din6aNad/RuD1M6Ig0fEl87Mr bfG7CIsO9Xf+H5xoIuq+dlAp4h+vRMVWZ9fljv14UeZvmhE5im4= =8igi -----END PGP SIGNATURE----- --hvbjw34mrhnklcex--