pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: cen <cen.is.imba@gmail.com>
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Postgres undeterministically uses a bad plan, how to convince it otherwise?
Date: Thu, 16 Feb 2023 16:47:52 +0100
Message-ID: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com> (raw)

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).


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.


Before providing and diving into the specific query plans:

- does the planner take previous runs of the same query and it's 
execution time into account? If not, why?

- assuming the query to be immutable, would it be possible for the 
planner to microbenchmark a few different plans instead of trying to 
estimate the cost?
As in, actually executing the query with different plans and caching the 
best one.



PS: good job to the FOSDEM Devroom organisers, one of the best rooms, 
was a blast.

Best regards, Cen






view thread (10+ messages)  latest in thread

Message-ID: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com>
Permalink:  ../9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com/
Also on:    postgresql.org/message-id/9c98978d-fc72-8655-bb29-5aaa941bee36@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: cen.is.imba@gmail.com
  Subject: Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?
  In-Reply-To: <9c98978d-fc72-8655-bb29-5aaa941bee36@gmail.com>
  References: <9c98978d-fc72-8655-bb29-5aaa941bee36@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