Knowledge Base : TshwaneDJe Software

ODBC: Performance Tuning a Large TLex / tlTerm / tlDatabase Database [Advanced]


PostgreSQL

• If performance on a very large database is slow (particularly with loading entries), check (using e.g. pgAdmin) if the index of the entry_id column of the "(prefix)_tree" table has 'substring' call in it - if so, delete and recreate the index of this column as follows, e.g.

DROP INDEX idx_YOURPREFIX_tree_2;
CREATE INDEX idx_YOURPREFIX_tree_2
 ON YOURPREFIX_tree
 USING btree
 (entry_id COLLATE pg_catalog."default");

This can significantly improve entry loading time in certain cases.
• If performance on a very large database is slow (particularly with loading entries), you could also try changing the postgresql.conf parameter cpu_tuple_cost to a higher value (e.g. 0.1, or 1 - default value is 0.01)

General

• Running the following (once-off) may help: Tools / Database administration / Optimise indexes