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.
