public inbox for [email protected]  

help / color / mirror / Atom feed

From: Quan Zongliang <[email protected]>
To: pgsql-hackers <[email protected]>
Subject: Incorrect estimation of HashJoin rows resulted from inaccurate small table statistics
Date: Fri, 16 Jun 2023 17:25:16 +0800
Message-ID: <[email protected]> (raw)


We have a small table with only 23 rows and 21 values.

The resulting MCV and histogram is as follows
stanumbers1 | {0.08695652,0.08695652}
stavalues1  | {v1,v2}
stavalues2  | 
{v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13,v14,v15,v16,v17,v18,v19,v20,v21}

An incorrect number of rows was estimated when HashJoin was done with 
another large table (about 2 million rows).

Hash Join  (cost=1.52..92414.61 rows=2035023 width=0) (actual 
time=1.943..1528.983 rows=3902 loops=1)

The reason is that the MCV of the small table excludes values with rows 
of 1. Put them in the MCV in the statistics to get the correct result.

Using the conservative samplerows <= attstattarget doesn't completely 
solve this problem. It can solve this case.

After modification we get statistics without histogram:
stanumbers1 | {0.08695652,0.08695652,0.04347826,0.04347826, ... }
stavalues1  | {v,v2, ... }

And we have the right estimates:
Hash Join  (cost=1.52..72100.69 rows=3631 width=0) (actual 
time=1.447..1268.385 rows=3902 loops=1)


Regards,

--
Quan Zongliang
Beijing Vastdata Co., LTD
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 52ef462dba..08ea4243f5 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -2518,7 +2518,7 @@ compute_scalar_stats(VacAttrStatsP stats,
 			{
 				/* Reached end of duplicates of this value */
 				ndistinct++;
-				if (dups_cnt > 1)
+				if (dups_cnt > 1 || samplerows <= num_mcv)
 				{
 					nmultiple++;
 					if (track_cnt < num_mcv ||


Attachments:

  [text/plain] analyze.diff (456B, ../[email protected]/2-analyze.diff)
  download | inline diff:
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index 52ef462dba..08ea4243f5 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -2518,7 +2518,7 @@ compute_scalar_stats(VacAttrStatsP stats,
 			{
 				/* Reached end of duplicates of this value */
 				ndistinct++;
-				if (dups_cnt > 1)
+				if (dups_cnt > 1 || samplerows <= num_mcv)
 				{
 					nmultiple++;
 					if (track_cnt < num_mcv ||

view thread (2+ messages)

reply

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: [email protected]
  Cc: [email protected]
  Subject: Re: Incorrect estimation of HashJoin rows resulted from inaccurate small table statistics
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox