From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kenneth Marshall <ktm@rice.edu>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL 12.3 slow index scan chosen
In-reply-to: <20200619204950.GL1497@aart.rice.edu>
References: <20200619194914.GI1497@aart.rice.edu>
 <1755412.1592597470@sss.pgh.pa.us> <20200619204950.GL1497@aart.rice.edu>
Comments: In-reply-to Kenneth Marshall <ktm@rice.edu>
	message dated "Fri, 19 Jun 2020 15:49:50 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1757511.1592600355.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 19 Jun 2020 16:59:15 -0400
Message-ID: <1757512.1592600355@sss.pgh.pa.us>
Precedence: bulk

Kenneth Marshall <ktm@rice.edu> writes:
> On Fri, Jun 19, 2020 at 04:11:10PM -0400, Tom Lane wrote:
>> The reason for such an estimation error usually is that the maximum
>> join key values recorded in pg_stats are off: the join side that is
>> going to be exhausted is the one with the smaller max join key.
>> "articles" seems to be small enough that the stats for it will be
>> exact, so your problem is a poor estimate of the max value of
>> objectcustomfieldvalues.objectid.  You might try raising the statistics
>> target for that table.  Or maybe it's just that ANALYZE hasn't been
>> done lately on one table or the other?

> Thank you for the information and suggestion. I tried bumping the statis=
tics for the
> objectcustomfieldvalues.objectid column to 2k, 5k and 10k followed by an=
 analyze and
> the query plan stayed the same. I also analyzed the article table
> repeatedly and their was no change in the plan. The table articles only =
has 151 rows
> while the objectcustomfieldvalues table has 19031909 rows. Any idea
> about why it is so far off?

What's the actual maximum value of objectcustomfieldvalues.objectid,
and how does that compare to the endpoint value in the pg_stats
histogram for that column?  If you've got one outlier in the table,
it might get missed by ANALYZE.

			regards, tom lane