Page 1 of 1

Database performance - Indexes

PostPosted: Wed Mar 20, 2019 3:25 pm
by sjoerd1337768711
I have a question about the loading/sorting queries of Servoy. Information on the page: https://wiki.servoy.com/display/DOCS/The+Servoy+Foundset

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.

Re: Database performance - Indexes

PostPosted: Tue Apr 16, 2019 11:51 am
by Joas
Your example-queries are all the same. I guess query 1 should only be ordered by 1 of the columns.

Ordering on 2 columns when there is no matching index is slow, like you said.
But what is your use case? A user can never see 1.7M records regardless of the order.
The user can only see a fraction of the data, so you should be looking into providing the right ways to filter the data. Then your indexes will also be effective again.