From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: Explain returns different number of rows
Date: Sat, 22 Oct 2022 11:32:32 +0200
Message-ID: <20221022093232.mrzvpsosul7sslyw@hjp.at> (raw)
In-Reply-To: <6B906119-82AD-49FC-A47D-8652FD03D936@thebuild.com>
References: <CAKS+vVb5CsvV_ae_=TVu6cRZTTWbGv1mhoyaBDPGLFQbQ9J=JQ@mail.gmail.com>
<CAKS+vVaCxnCLpEruhkqwPWsh2YrhmL_3juriPP0ODwWDPfBR_g@mail.gmail.com>
<CAKS+vVZuoYhwk1qkqFo6RCraCFbK2q1Td3OQqy8QxWRNZfZN5A@mail.gmail.com>
<6B906119-82AD-49FC-A47D-8652FD03D936@thebuild.com>
On 2022-10-20 09:56:23 -0700, Christophe Pettus wrote:
> On Oct 20, 2022, at 09:52, Vince McMahon <sippingonesandzeros@gmail.com> wrote:
> > The number of rows are different.
>
> 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=> explain (analyze, buffers) select * from facttable_eurostat_comext_cpa2_1 ;
╔══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ QUERY PLAN ║
╟──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╢
║ Seq Scan on facttable_eurostat_comext_cpa2_1 (cost=0.00..1005741.32 rows=39633432 width=85) (actual time=0.396..6541.701 rows=39633591 loops=1) ║
║ Buffers: shared read=609407 ║
║ Planning Time: 1.650 ms ║
║ Execution Time: 7913.027 ms ║
╚══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝
(4 rows)
#v-
The first tuple (cost=0.00..1005741.32 rows=39633432 width=85) is an
estimate used to plan the query. But the second one
(actual time=0.396..6541.701 rows=39633591 loops=1)
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
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
Attachments:
[application/pgp-signature] signature.asc (832B, ../20221022093232.mrzvpsosul7sslyw@hjp.at/2-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hjp-pgsql@hjp.at, pgsql-general@lists.postgresql.org
Subject: Re: Explain returns different number of rows
In-Reply-To: <20221022093232.mrzvpsosul7sslyw@hjp.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox