Re: Postgres undeterministically uses a bad plan, how to convince it otherwise?

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.