Re: PostgreSQL 12.3 slow index scan chosen
On 2020-Jun-20, Tom Lane wrote: > I wrote: > > ... oh, now I see: apparently, your filter condition is such that *no* > > rows of the objectcustomfieldvalues table get past the filter: > > > > -> Index Scan using objectcustomfieldvalues3 on > > objectcustomfieldvalues objectcustomfieldvalues_1 (cost=0.56..807603.40 > > rows=915 width=4) (actual time=21165.441..21165.441 rows=0 loops=1) > > Filter: ((disabled = 0) AND ((largecontent ~~* > > '%958575%'::text) OR ((content)::text ~~* '%958575%'::text))) > > Rows Removed by Filter: 19030904 > 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? I thought not. Maybe a trigram search would help? See contrib/pg_trgm -- as far as I remember that module is able to work with LIKE conditions. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: PostgreSQL 12.3 slow index scan chosen
On Mon, Jun 22, 2020 at 03:27:32PM -0400, Alvaro Herrera wrote: > On 2020-Jun-20, Tom Lane wrote: > > > I wrote: > > > ... oh, now I see: apparently, your filter condition is such that *no* > > > rows of the objectcustomfieldvalues table get past the filter: > > > > > > -> Index Scan using objectcustomfieldvalues3 on > > > objectcustomfieldvalues objectcustomfieldvalues_1 (cost=0.56..807603.40 > > > rows=915 width=4) (actual time=21165.441..21165.441 rows=0 loops=1) > > > Filter: ((disabled = 0) AND ((largecontent ~~* > > > '%958575%'::text) OR ((content)::text ~~* '%958575%'::text))) > > > Rows Removed by Filter: 19030904 > > > 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? I thought > not. Maybe a trigram search would help? See contrib/pg_trgm -- as far > as I remember that module is able to work with LIKE conditions. > Hi Alvaro, I do have a pg_trgm GIN index on those fields for the search. Regards, Ken
Re: PostgreSQL 12.3 slow index scan chosen
Alvaro Herrera 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
