From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Kenneth Marshall <ktm(at)rice(dot)edu> |
Cc: | pgsql-performance(at)lists(dot)postgresql(dot)org |
Subject: | Re: PostgreSQL 12.3 slow index scan chosen |
Date: | 2020-06-19 20:59:15 |
Message-ID: | 1757512.1592600355@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Kenneth Marshall <ktm(at)rice(dot)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 statistics 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
From | Date | Subject | |
---|---|---|---|
Next Message | Kenneth Marshall | 2020-06-19 21:23:38 | Re: PostgreSQL 12.3 slow index scan chosen |
Previous Message | Kenneth Marshall | 2020-06-19 20:49:50 | Re: PostgreSQL 12.3 slow index scan chosen |