I upgraded a TDE database to PgSQL 17 from PgSQL 12. My non-TDE pgsql 17 databases work fine. However, the TDE one I upgraded has the problem that query planner takes too long. Details below -
DB size: 4T
No. of tables - 10K
Largest tables - 100M tuples
The planning time for query on pgsql 17 v 12, TDE-enabled
s14=> explain analyze select invoicenum from ltlinvoice where pseq=1400000224216382;
QUERY PLAN
Index Scan using ltlinvoice_pseq on ltlinvoice (cost=0.43..10.10 rows=9 width=12) (actual time=0.040..0.040 rows=0 loops=1)
Index Cond: (pseq = ‘1400000224216382’::bigint)
Planning Time: 158.199 ms
Execution Time: 0.051 ms
s14=> explain analyze select invoicenum from ltlinvoice where pseq=1400000224216382;
QUERY PLAN
Index Scan using ltlinvoice_pseq on ltlinvoice (cost=0.43..10.10 rows=9 width=12) (actual time=0.023..0.023 rows=0 loops=1)
Index Cond: (pseq = ‘1400000224216382’::bigint)
Planning Time: 0.195 ms
Execution Time: 0.046 ms
Please advise about the reason for this problem and potential solutions.