pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.





view thread (10+ messages)  latest in thread

Message-ID: <9641fdf1-a4d1-2fad-2400-794dcf7dc5cc@gmail.com>
Permalink:  ../9641fdf1-a4d1-2fad-2400-794dcf7dc5cc@gmail.com/
Also on:    postgresql.org/message-id/9641fdf1-a4d1-2fad-2400-794dcf7dc5cc@gmail.com

 · 

reply

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