xTuple.com xTupleU Blog & News Customer Support

Observations on the contacts-detail metasql in 5.0

I don’t know where to post this sort of info without the old “issues” tool. I’m just putting it here and hope the the right people will be advised.

There is a metaql record named contacts-detail is executing during some major slowdowns of pilot project. One thing I notice within that metasql statement is there is a CTE expression that uses a primary table of “cntct()”. Precisely Line 76 is “FROM chtct()”. I think this causes problems with the query optimizer. We know that the table cntct has indexes. However how does that info impact the query building when the primary FROM table is a function? The cntct() function only provides restrictions based on permissions. I’m assuming that it is returning the entire cntct dataset with no indexes. When I change line 76 to “FROM cntct” instead of “FROM cntct()” then the execution time of a sample queries drops from 5.21 MINUTES to 2.45 MINUTES. I hope this is helpful info.



Yes, the cntct() function makes the query optimizer unhappy. The alternative is to use the table directly, as you noted. If hiding data based on record “ownership” is important, it has to be accounted for in the query somewhere.

Here and for your related post, the query execution plan will tell you where the time is being spent. Reading QEPs takes a lot of practice but it’s worthwhile for anyone who writes or debugs a lot of queries. If record hiding significantly affects the size of the result set for different users, consider adding an index on the cntct_owner_username column:

SELECT xt.add_index('cntct', 'cntct_owner_username', 'cntct_cntct_owner_username_idx', 'btree', 'public');

Again, let us know what happens.