From: Stephen Frost <sfrost@snowman.net>
To: Charles <peacech@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Query run in 27s with 15.2 vs 37ms with 14.6
Date: Mon, 20 Feb 2023 15:58:36 -0500
Message-ID: <Y/Pe/Ed7HNBKUN0q@tamriel.snowman.net> (raw)
In-Reply-To: <CABthHP_KBYvDvPEfrGEtP=ghbPKrxD5asAaHRX1F4ajJ_=-vcw@mail.gmail.com>
References: <CABthHP8Maot3JBq6jMfcrW0Y2xPftnQsi=2qCPQ=SkKbqnkt6g@mail.gmail.com>
<3573388.1676909470@sss.pgh.pa.us>
<CABthHP-HemR9kK_ihnPnXqoBYk3Y3X_Vrsai8=mKEyAoib8T8A@mail.gmail.com>
<CABthHP_KBYvDvPEfrGEtP=ghbPKrxD5asAaHRX1F4ajJ_=-vcw@mail.gmail.com>
Greetings,
* Charles (peacech@gmail.com) wrote:
> Wrapping the query with a select * from (...) t where length(code) = 4 puts
> the execution time back to 27 seconds.
>
> This is a bit unexpected since I expect that the result from the inner
> query to be executed first and then filtered.
It's really not- PG will (correctly) attempt to pull in such subselects
into the overall optimization, which is generally better for everyone.
If you want to force it, you can use a WITH MATERIALIZED CTE, or throw
in an 'OFFSET 0' as a hack into your sub-select, but really it's a much
better idea to generate extended stats on what you're filtering as has
been suggested, or come up with a better data representation where
you're not doing a search on a 'length()' as you are.
Thanks,
Stephen
Attachments:
signature.asc (application/pgp-signature, 832B) download
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-bugs@postgresql.org
Cc: sfrost@snowman.net, peacech@gmail.com, tgl@sss.pgh.pa.us, pgsql-bugs@lists.postgresql.org
Subject: Re: Query run in 27s with 15.2 vs 37ms with 14.6
In-Reply-To: <Y/Pe/Ed7HNBKUN0q@tamriel.snowman.net>
* 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