From: Tom Lane <tgl@sss.pgh.pa.us>
To: Anders Steinlein <anders@e5r.no>
cc: pgsql-performance@lists.postgresql.org
Subject: Re: Slow planning time when public schema included (12 vs. 9.4)
In-reply-to: 
 <CAC35HN=tS6zDy0FcQ-mPjsZ9eO_37fe9m3mLDQ+zWZWrqEB9rw@mail.gmail.com>
References: 
 <CAC35HN=tS6zDy0FcQ-mPjsZ9eO_37fe9m3mLDQ+zWZWrqEB9rw@mail.gmail.com>
Comments: In-reply-to Anders Steinlein <anders@e5r.no>
	message dated "Sat, 21 Mar 2020 13:02:10 +0100"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <2803.1584800777.1@sss.pgh.pa.us>
Date: Sat, 21 Mar 2020 10:26:17 -0400
Message-ID: <2804.1584800777@sss.pgh.pa.us>
Precedence: bulk

Anders Steinlein <anders@e5r.no> writes:
> We haven't noticed any issues with this before now, until we started seeing
> really slow planning time on some relatively simple queries:
> ...
> The planning time is the same even if running the same query multiple times
> within the same session. When having only the tenant's schema in the
> search_path, planning time is much improved:

I notice a difference in these plans:

>  Hash Join  (cost=452.96..1887.72 rows=1518 width=41) (actual
> time=6.581..18.845 rows=2945 loops=1)
>    Hash Cond: ((cs.email)::citext = (cl.email)::citext)
                             ^^^^^^               ^^^^^^

>  Hash Join  (cost=452.96..1887.72 rows=1517 width=41) (actual
> time=3.980..8.554 rows=2945 loops=1)
>    Hash Cond: ((cs.email)::text = (cl.email)::text)
                             ^^^^               ^^^^

I think what is happening is that the "cl.email = cs.email" clause
is resolving as a different operator depending on your search path;
probably there is a "citext = citext" operator in the public
schema, and if available the parser will think it's a better match
than the "text = text" operator.  However, "citext = citext" can
be orders of magnitude slower, depending on what locale settings
you're using.  That's affecting your planning time (since the
planner will apply the operator to the values available from
pg_stats), and it's also visibly affecting the query runtime.

Not sure why you'd not have seen the same effect in your 9.4
installation, but maybe you had citext installed somewhere else?

			regards, tom lane