From: Ron <ronljohnsonjr@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?
Date: Thu, 16 Feb 2023 10:15:50 -0600
Message-ID: <9641fdf1-a4d1-2fad-2400-794dcf7dc5cc@gmail.com> (raw)
In-Reply-To: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com>
References: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com>
On 2/16/23 09:47, cen wrote:
> Hi,
>
> I am running the same application (identical codebase) as two separate
> instances to index (save) different sets of data. Both run PostgreSQL 13.
>
> The queries are the same but the content in actual databases is different.
> One database is around 1TB and the other around 300GB.
>
>
> There is a problem with a paginated select query with a join and an order.
> Depending on which column you order by (primary or FK) the query is either
> instant or takes minutes.
>
> So on one database, ordering by primary is instant but on the other it is
> slow and vice-versa. Switching the columns around on the slow case fixes
> the issue.
>
> All relavant colums are indexed.
>
>
> Simplified:
>
> Slow: SELECT * from table1 AS t1 LEFT JOIN table2 AS t2 ON t2.t1_id=t1.id
> ORDER BY t1.id ASC LIMIT 0, 10
>
> Fast: SELECT * from table1 AS t1 LEFT JOIN table2 AS t2 ON t2.t1_id=t1.id
> ORDER BY t2.t1_id ASC LIMIT 0, 10
>
> (and the opposite, on the other instance the first one is fast and second
> one is slow).
What does EXPLAIN ANALYZE say?
> I have run all the statistic recalculations but that doesn't help. As far
> as I could read the docs, there is no real way to affect the plan
>
> other than reworking the query (I've read about fencing?) which can't be
> done because it seems to be unpredictable and depends on actual data and
> data quantity.
>
> I haven't tried reindexing.
Since you've run ANALYZE, when were the tables last vacuumed?
--
Born in Arizona, moved to Babylonia.
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: ronljohnsonjr@gmail.com
Subject: Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?
In-Reply-To: <9641fdf1-a4d1-2fad-2400-794dcf7dc5cc@gmail.com>
References: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com> <9641fdf1-a4d1-2fad-2400-794dcf7dc5cc@gmail.com>
* 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