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)
Date: Sat, 21 Mar 2020 10:26:17 -0400
Message-ID: <2804.1584800777@sss.pgh.pa.us> (raw)
In-Reply-To: <CAC35HN=tS6zDy0FcQ-mPjsZ9eO_37fe9m3mLDQ+zWZWrqEB9rw@mail.gmail.com>
References: <CAC35HN=tS6zDy0FcQ-mPjsZ9eO_37fe9m3mLDQ+zWZWrqEB9rw@mail.gmail.com>
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
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, anders@e5r.no, pgsql-performance@lists.postgresql.org
Subject: Re: Slow planning time when public schema included (12 vs. 9.4)
In-Reply-To: <2804.1584800777@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