From: Tom Lane <tgl@sss.pgh.pa.us>
To: Charles <peacech@gmail.com>
cc: pgsql-bugs@lists.postgresql.org
Subject: Re: Query run in 27s with 15.2 vs 37ms with 14.6
In-reply-to: 
 <CABthHP8Maot3JBq6jMfcrW0Y2xPftnQsi=2qCPQ=SkKbqnkt6g@mail.gmail.com>
References: 
 <CABthHP8Maot3JBq6jMfcrW0Y2xPftnQsi=2qCPQ=SkKbqnkt6g@mail.gmail.com>
Comments: In-reply-to Charles <peacech@gmail.com>
	message dated "Mon, 20 Feb 2023 22:55:52 +0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <3573387.1676909470.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Mon, 20 Feb 2023 11:11:10 -0500
Message-ID: <3573388.1676909470@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/3573388.1676909470%40sss.pgh.pa.us>
Precedence: bulk

Charles <peacech@gmail.com> writes:
> I have just upgraded my postgresql from 14.6 to 15.2 and my query that r=
uns
> in 37ms in 14.6 run requires 27 seconds to complete. The table is a typi=
cal
> OHLC table (date date, code text, open int, high int, low int, close int=
,
> volume bigint, value bigint), 2725207 rows in table.

You need to do something to fix this poor row-count estimate:

>   ->  Index Scan using idx_stock_price_date on stock_price s
>  (cost=3D0.43..1152.53 rows=3D1 width=3D29) (actual time=3D0.180..4.060 =
rows=3D779 loops=3D1)
>         Index Cond: (date =3D '2023-02-20'::date)
>         Filter: ((open > 0) AND (value > 0) AND (length(code) =3D 4))
>         Rows Removed by Filter: 210

Considering that 14.x also estimated this as returning only one row,
I'm fairly surprised that you didn't get the same poor plan choice there.

The length(code) condition suggests a fairly poor choice of data
representation; can you change that?  In any case, the planner does
not keep statistics that would help it estimate that.  It might be
useful to try defining extended statistics on length(code) as such,
or if that doesn't help then on the combination of open, value,
and length(code).  But you need to get that estimate up to at least
several rows to discourage the choice of nestloop join.

			regards, tom lane