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: <20200619224302.GP1497@aart.rice.edu>
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>
Comments: In-reply-to Kenneth Marshall <ktm@rice.edu>
	message dated "Fri, 19 Jun 2020 17:43:02 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1762065.1592608033.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Fri, 19 Jun 2020 19:07:13 -0400
Message-ID: <1762066.1592608033@sss.pgh.pa.us>
Precedence: bulk

[ please keep the mailing list cc'd ]

Kenneth Marshall <ktm@rice.edu> writes:
> Here are the stats for articles.id:

> 4,7,9,11,13,14,16,17,18,19,20,21,22,23,
> 24,25,26,32,33,34,36,40,41,42,43,44,49,50,51,52,53,54,55,56,57,58,59,60,=
61,62,63,99,100,101,102,106,107,108,109,113,1 14,115,116,117,118,119,120,1=
21,122,123,125,126,127,128,129,130,131,133,134,135,136,137,140,141,142,143=
,144,145,146,14 7,148,149,150,151,152,153,155,156,157,158,159,160,161,162,=
163,164,165,166,167,168,169,170,171,172,173,174,175,176,177 ,178,179,180,1=
81,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199=
,200,201,202,203,204,205,206, 207,208,1209,1210,1212,1213,1214,1215,1216,1=
219,1220,1221,1222,1223}          =

> That completely matches the max(id) for articles.id.

Hm, well it's clear why the planner is going for the mergejoin strategy:
it expects to only have to scan a very small fraction of the other table
before it's up past objectid =3D 1223 and can stop merging.  And it
seems like it's right ...

... 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 objectcusto=
mfieldvalues objectcustomfieldvalues_1  (cost=3D0.56..807603.40 rows=3D915=
 width=3D4) (actual time=3D21165.441..21165.441 rows=3D0 loops=3D1)
                     Filter: ((disabled =3D 0) AND ((largecontent ~~* '%95=
8575%'::text) OR ((content)::text ~~* '%958575%'::text)))
                     Rows Removed by Filter: 19030904

"rows=3D0" is the telltale.  So even after we're past objectid =3D 1223, t=
hat
scan continues, because the mergejoin needs to see a higher key before it
knows it can stop.

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.
(It does look like the planner is correctly estimating that the
filter is quite selective --- it's just not considering the potential
impact on the scan-until-finding-a-greater-key behavior.)

Right now I don't have any better suggestion than disabling mergejoin
if you think the filter is going to be very selective.

			regards, tom lane