Database performance - Indexes

Questions and answers regarding general SQL and backend databases

Database performance - Indexes

Postby sjoerd1337768711 » Wed Mar 20, 2019 3:25 pm

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.
Sjoerd Hannema
Muta Services B.V.
sjoerd1337768711
 
Posts: 18
Joined: Wed May 23, 2012 12:25 pm
Location: Leeuwarden, NL

Re: Database performance - Indexes

Postby Joas » Tue Apr 16, 2019 11:51 am

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.
Joas de Haan
Yield Software Development
Need help on your project? yieldsd.com
User avatar
Joas
Site Admin
 
Posts: 842
Joined: Mon Mar 20, 2006 4:07 pm
Location: Leusden, NL


Return to SQL Databases

Who is online

Users browsing this forum: Bing [Bot] and 7 guests

cron