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
Date: Mon, 20 Feb 2023 11:11:10 -0500
Message-ID: <3573388.1676909470@sss.pgh.pa.us> (raw)
In-Reply-To: <CABthHP8Maot3JBq6jMfcrW0Y2xPftnQsi=2qCPQ=SkKbqnkt6g@mail.gmail.com>
References: <CABthHP8Maot3JBq6jMfcrW0Y2xPftnQsi=2qCPQ=SkKbqnkt6g@mail.gmail.com>
Charles <peacech@gmail.com> writes:
> I have just upgraded my postgresql from 14.6 to 15.2 and my query that runs
> in 37ms in 14.6 run requires 27 seconds to complete. The table is a typical
> 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=0.43..1152.53 rows=1 width=29) (actual time=0.180..4.060 rows=779 loops=1)
> Index Cond: (date = '2023-02-20'::date)
> Filter: ((open > 0) AND (value > 0) AND (length(code) = 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
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: tgl@sss.pgh.pa.us, peacech@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: Query run in 27s with 15.2 vs 37ms with 14.6
In-Reply-To: <3573388.1676909470@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