Date: Mon, 20 Feb 2023 15:58:36 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Charles <peacech@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-bugs@lists.postgresql.org
Subject: Re: Query run in 27s with 15.2 vs 37ms with 14.6
Message-ID: <Y/Pe/Ed7HNBKUN0q@tamriel.snowman.net>
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="GNYoGczoL34O0sNZ"
Content-Disposition: inline
In-Reply-To: 
 <CABthHP_KBYvDvPEfrGEtP=ghbPKrxD5asAaHRX1F4ajJ_=-vcw@mail.gmail.com>
User-Agent: Mutt/2.1.4 (2021-12-11)
Archived-At: 
 <https://www.postgresql.org/message-id/Y/Pe/Ed7HNBKUN0q%40tamriel.snowman.net>
Precedence: bulk


--GNYoGczoL34O0sNZ
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Charles (peacech@gmail.com) wrote:
> Wrapping the query with a select * from (...) t where length(code) =3D 4 =
puts
> the execution time back to 27 seconds.
>=20
> 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

--GNYoGczoL34O0sNZ
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----

iQIzBAABCgAdFiEEwf6gbxKhD863zrx/7WyKOINHZFUFAmPz3vgACgkQ7WyKOINH
ZFW+FA/+P82hhXoq380l/ykroDyQJz+tmI1/Ym3RTs4CnJdE1QNsTjeCtf4wl1Sz
1jECm4pr6Lku6MPfHBuj25a1J+fHs6WbYseIaW5Qf9qqLGwnH3wTQ3M5RHb8huia
b75ub58X+1CPCzVSOGvN3QVR6ekKbP4gnUDW2OZEkROOby+77QfklWvFPkBYbmTu
7eZrthPbBzmSRlWFQuMUnSzpOJur4gtHR1YnYdj3TiIdrKRA0008qIvD380/7ZwT
jeV+HuD76kgoS3ksoxwsOFlasJVVH6Z847b4Ls3KVsgMHbntr9oYnaVPYlzzvUsc
DWsz0Puz0TCjuLQjo/MIEopmFKTkVuoU5KqH6QpaAgQ60qBeBQ7yCkwjGleI8sno
05OtyBM1hNMEhlmNCQN/RgGyxlWy7QMmOjP1EDSoERT0lan3/dkQcUmJCin0R2R+
fNammrb+bqbMJysVEo0Xpu0BiDvFlkaNm8D5+dDh2hjIJcolwU93vV5wRIjVU+Ek
aSOEyjW6QzUZmPJmU9ykTMhIajH1AOHhqEL/87UT+ro1v+ks2QPwhYwFgsjSNO6v
obMgc/g+L6CLK0uXEm8pPHtsdI4Y32lHO8rG6WtUsnPUAVvWgj5TDYfdFT0Vbz73
Z8yXMH6gi5uxtI3UwKVTfEve6hKSiTFuYu4dJ2zntzpfKSqG7wc=
=ahjU
-----END PGP SIGNATURE-----

--GNYoGczoL34O0sNZ--