xTuple.com xTupleU Blog & News Customer Support

Address query performance

We have 199,243 rows in the addr table that are creating performance problems when opening a new CRM account. It is taking over 16 seconds for a new CRM account screen to open. From the log I found the culprit to be address-detail.mql that is pulling all addresses. We do not have any custom versions of this metasql that would interfere with the performance. I am looking for suggestions on how to improve the performance of this query. Any ideas?

The log excerpt is below.

2020-08-10 14:50:44 EDT [29321]: db=wwtest,user=rshapiro,app=xTuple ERP (qt-client),client=192.168.131.53 LOG:  duration: 16158.058 ms  statement: WITH chartext AS (SELECT charass_target_id, charass_char_id,
	                          string_agg(charass_value, ',') AS charass_value
	                     FROM addr
	                     JOIN charass ON charass_target_type = 'ADDR'
	                                 AND addr_id = charass_target_id
	                    WHERE charass_char_id IN (-1
	                                              
	                                             )
	                    GROUP BY charass_target_id, charass_char_id),
	      charlist AS (SELECT charass_target_id, charass_char_id,
	                          string_agg(charass_value, ',') AS charass_value
	                     FROM addr
	                     JOIN charass ON charass_target_type = 'ADDR'
	                                 AND addr_id = charass_target_id
	                    WHERE charass_char_id IN (-1
	                                              
	                                             )
	                    GROUP BY charass_target_id, charass_char_id),
	      chardate AS (SELECT charass_target_id, charass_char_id,
	                          MIN(charass_value::DATE) AS charass_value
	                     FROM addr
	                     JOIN charass ON charass_target_type = 'ADDR'
	                                 AND addr_id = charass_target_id
	                    WHERE charass_char_id IN (-1
	                                              
	                                             )
	                    GROUP BY charass_target_id, charass_char_id),
	_addresses AS (
	SELECT addr_id, addr_line1, addr_line2, addr_line3,
	  regexp_replace(array_agg(crmacct_number)::TEXT, '\{|\}|\"|NULL', '', 'g')  as crmacct,
	  addr_city, addr_state, formatcountryname(addr_country) AS addr_country, addr_postalcode
	FROM addr
	  LEFT JOIN crmacctaddrass ON crmacctaddrass_addr_id=addr_id
	  LEFT JOIN crmacct ON crmacctaddrass_crmacct_id=crmacct_id
	WHERE true
	 AND addr_active 
	GROUP BY addr_id, addr_line1, addr_line2, addr_line3, addr_country, addr_state, 
	         addr_city, addr_line1, addr_number, addr_postalcode
	ORDER BY addr_country, addr_state, addr_city, addr_line1, addr_number
	)
	SELECT * FROM _addresses
	;.

The EXPLAIN plan on the query shows that it is not using any indexes but is sequence scanning all tables.

EXPLAIN WITH chartext AS (SELECT charass_target_id, charass_char_id,
	                          string_agg(charass_value, ',') AS charass_value
	                     FROM addr
	                     JOIN charass ON charass_target_type = 'ADDR'
	                                 AND addr_id = charass_target_id
	                    WHERE charass_char_id IN (-1
	                                              
	                                             )
	                    GROUP BY charass_target_id, charass_char_id),
	      charlist AS (SELECT charass_target_id, charass_char_id,
	                          string_agg(charass_value, ',') AS charass_value
	                     FROM addr
	                     JOIN charass ON charass_target_type = 'ADDR'
	                                 AND addr_id = charass_target_id
	                    WHERE charass_char_id IN (-1
	                                              
	                                             )
	                    GROUP BY charass_target_id, charass_char_id),
	      chardate AS (SELECT charass_target_id, charass_char_id,
	                          MIN(charass_value::DATE) AS charass_value
	                     FROM addr
	                     JOIN charass ON charass_target_type = 'ADDR'
	                                 AND addr_id = charass_target_id
	                    WHERE charass_char_id IN (-1
	                                              
	                                             )
	                    GROUP BY charass_target_id, charass_char_id),
	_addresses AS (
	SELECT addr_id, addr_line1, addr_line2, addr_line3,
	  regexp_replace(array_agg(crmacct_number)::TEXT, '\{|\}|\"|NULL', '', 'g')  as crmacct,
	  addr_city, addr_state, formatcountryname(addr_country) AS addr_country, addr_postalcode
	FROM addr
	  LEFT JOIN crmacctaddrass ON crmacctaddrass_addr_id=addr_id
	  LEFT JOIN crmacct ON crmacctaddrass_crmacct_id=crmacct_id
	WHERE true
	 AND addr_active 
	GROUP BY addr_id, addr_line1, addr_line2, addr_line3, addr_country, addr_state, 
	         addr_city, addr_line1, addr_number, addr_postalcode	
	ORDER BY addr_country, addr_state, addr_city, addr_line1, addr_number
	)
	SELECT * FROM _addresses

CTE Scan on _addresses  (cost=108150.57..112135.43 rows=199243 width=260)
  CTE _addresses
    ->  Sort  (cost=107652.46..108150.57 rows=199243 width=113)
          Sort Key: (formatcountryname(addr.addr_country)), addr.addr_state, addr.addr_city, addr.addr_line1, addr.addr_number
          ->  GroupAggregate  (cost=34824.99..90114.93 rows=199243 width=113)
                Group Key: addr.addr_id
                ->  Sort  (cost=34824.99..35323.10 rows=199243 width=57)
                      Sort Key: addr.addr_id
                      ->  Hash Right Join  (cost=13042.50..17287.46 rows=199243 width=57)
                            Hash Cond: (crmacctaddrass.crmacctaddrass_addr_id = addr.addr_id)
                            ->  Hash Left Join  (cost=5655.53..9407.51 rows=187799 width=12)
                                  Hash Cond: (crmacctaddrass.crmacctaddrass_crmacct_id = crmacct.crmacct_id)
                                  ->  Seq Scan on crmacctaddrass  (cost=0.00..3258.99 rows=187799 width=8)
                                  ->  Hash  (cost=3623.57..3623.57 rows=162557 width=12)
                                        ->  Seq Scan on crmacct  (cost=0.00..3623.57 rows=162557 width=12)
                            ->  Hash  (cost=4896.43..4896.43 rows=199243 width=49)
                                  ->  Seq Scan on addr  (cost=0.00..4896.43 rows=199243 width=49)
                                        Filter: addr_active

Rebecca,

Make sure the following two indexes exist: crmacctaddrass_addr_id_idx and crmacctaddrass_crmacct_id_idx on the crmacctaddrass table. If they don’t exist, create them:

SELECT xt.add_index('crmacctaddrass', 'crmacctaddrass_addr_id', 'crmacctaddrass_addr_id_idx', 'btree', 'public'),
       xt.add_index('crmacctaddrass', 'crmacctaddrass_crmacct_id', 'crmacctaddrass_crmacct_id_idx', 'btree', 'public');

If they do exist already, try rebuilding them:

REINDEX INDEX crmacctaddrass_addr_id_idx;
REINDEX INDEX crmacctaddrass_crmacct_id_idx;

If that still doesn’t help then we’ll have to look further.

Gil

One more thing… there’s no WHERE clause in the query. That alone may explain why no indexes are being used — the server has to read the entire table and it’s faster to read the whole thing sequentially than using an index.

Do you have any scripts named crmaccount or addresses?

We have custom versions of the following crmaccount scripts: crmaccount.js, crmaccounts.js, crmaccounts-detail.mql. Nothing for addresses. I will try REINDEXing.

No change with the REINDEX. I agree that the lack of a WHERE would drive the query planner to do a sequential scan but we have a lot of addresses. I was wondering about partitioning this table…

Rebecca,

At this point this sounds like a product bug — there’s supposed to be a filter based on the CRM account’s id in this context. It’s not a performance problem, it’s a “we’re running the wrong query” problem.

Contact support directly about this so there’s a clear channel of communication for follow-up. Try disabling your crmaccount.js script first just to be sure that’s not playing a role.

Gil

I disabled our crmaccount.js script and there was no change. I have also created a support ticket. Can you provide us with a fix when you figure it out? We are running 5.1. Thanks.