Loading Records
One of the primary jobs of a foundset is to load records from the table to which it is bound. A foundset is always based on an underlying SQL query, which may change often during the lifetime of the foundset. However the query will always take the form of selecting the Primary Key column(s) from the table and will also always include an ORDER BY clause, which in its simplest form will sort the results based on the Primary Key column(s).
I experience problems in speed on PostgreSQL 11.1. The table has around 1.700.000 rows. Indexes on all fields in the where clause and order by.
The queries are very slow when the order by is on more than one single column.
Information about this slowness explained: https://dba.stackexchange.com/questions/178344/order-by-on-two-columns-very-slow-compared-to-order-by-on-a-single-column
Because of the ORDER BY is on more columns the indexes are not working properly. Extra combined indexes can solve the speed. Indexes which are combined all columns in the ORDER BY. Results of the performance is OK.
Servoy is using the ORDER BY to limit the loaded records and load extra blocks on PK level.
Speed difference:
1: select crm_businesse_id from crm_businesses where (delete_mark = '0' or delete_mark is null) order by crm_businessname_short desc, crm_businesse_id asc limit 10
--> Query runtime: 20 secs
2: select crm_businesse_id from crm_businesses where (delete_mark = '0' or delete_mark is null) order by crm_businessname_short desc, crm_businesse_id asc limit 10
--> Query runtime: 89 milliseconds
Extra index combined with columns crm_businessname_short desc and crm_businesse_id asc :
3: select crm_businesse_id from crm_businesses where (delete_mark = '0' or delete_mark is null) order by crm_businessname_short desc, crm_businesse_id asc limit 10
--> Query runtime: 86 milliseconds
For option 3 I would need to create a lot of extra combined indexes because a user can sort on different columns in the table-view. Also you need to create double indexes for ASC and DESC.
Is there a better way to improve the performance with PostgreSQL/Indexes ?
Please advice.