From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alvaro Herrera <alvherre@2ndquadrant.com>
Cc: Kenneth Marshall <ktm@rice.edu>
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL 12.3 slow index scan chosen
Date: Mon, 22 Jun 2020 15:39:42 -0400
Message-ID: <2087061.1592854782@sss.pgh.pa.us> (raw)
In-Reply-To: <20200622192732.GA29072@alvherre.pgsql>
References: <20200622192732.GA29072@alvherre.pgsql>
Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> On 2020-Jun-20, Tom Lane wrote:
>> You said you'd increased the stats target for
>> objectcustomfieldvalues.objectid, but maybe the real problem is needing
>> to increase the targets for content and largecontent, in hopes of driving
>> down the estimate for how many rows will pass this filter condition.
> ... but those on content and largecontent are unanchored conditions --
> are we still able to do any cardinality analysis using those?
Yes, if the stats histogram is large enough we'll apply it by just
evaluating the query operator verbatim on each entry (thereby assuming
that the histogram is usable as a random sample). And we apply the
query condition on each MCV entry too (no assumptions needed there).
The unanchored LIKE conditions could not be used as btree indexquals,
but that has little to do with selectivity estimation.
Since we bound those things at 10K entries, the histogram alone can't give
better than 0.01% estimation precision, which in itself wouldn't have
done the job for the OP -- he needed a couple more places of accuracy
than that. I surmise that he had a nontrivial MCV population as well,
since he found that raising the stats target did eventually drive down
the estimate far enough to fix the problem.
regards, tom lane
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: tgl@sss.pgh.pa.us, alvherre@2ndquadrant.com, ktm@rice.edu, pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL 12.3 slow index scan chosen
In-Reply-To: <2087061.1592854782@sss.pgh.pa.us>
* 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