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
Date: Sat, 20 Jun 2020 14:22:03 -0400
Message-ID: <1815407.1592677323@sss.pgh.pa.us> (raw)
In-Reply-To: <1762066.1592608033@sss.pgh.pa.us>
References: <20200619194914.GI1497@aart.rice.edu>
<1755412.1592597470@sss.pgh.pa.us>
<20200619204950.GL1497@aart.rice.edu>
<1757512.1592600355@sss.pgh.pa.us>
<20200619212338.GM1497@aart.rice.edu>
<1759984.1592604634@sss.pgh.pa.us>
<20200619222533.GN1497@aart.rice.edu>
<1760785.1592605845@sss.pgh.pa.us>
<20200619224302.GP1497@aart.rice.edu>
<1762066.1592608033@sss.pgh.pa.us>
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
> That's kind of annoying :-(. I wonder if there's a way to be smarter?
> This case would work a lot better if the filter conditions were not
> applied till after the merge; but of course that wouldn't be an
> improvement in general. Or maybe we should penalize the mergejoin
> cost estimate if there's a highly selective filter in the way.
I experimented with this some more, with the intention of creating a
planner patch that would do the latter, and was surprised to find that
there already is such a penalty. It's sort of indirect and undocumented,
but nonetheless the estimate for whether a mergejoin can stop early
does get heavily de-rated if the planner realizes that the table is
being heavily filtered. So now I'm thinking that your problem is that
"rows=915" is not a good enough estimate of what will happen in this
indexscan. It looks good in comparison to the table size of 19M rows,
but on a percentage basis compared to the true value of 0 rows, it's
still pretty bad. 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.
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, ktm@rice.edu, pgsql-performance@lists.postgresql.org
Subject: Re: PostgreSQL 12.3 slow index scan chosen
In-Reply-To: <1815407.1592677323@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