New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

0013732: After upgrade to 2017.08.11, syncing our mobile devices using CardDAV is killing PostgreSQL using 100% CPU for a query... #6792

Open
Gloirin opened this Issue Jun 9, 2018 · 3 comments

Comments

Projects
None yet
2 participants
@Gloirin

Gloirin commented Jun 9, 2018

Reported by nbe-renzel-net on 27 Jan 2018 00:57

Version: 2017.08.11 Community Edition

After upgrading to 2017.08.11, during CardDAV sync (via DAVdroid), the following query takes extremely long and is killing PostgreSQL using 100% CPU per sync session.

This is the query:

LOG: duration: 2794.698 ms execute pdo_stmt_000039a1: SELECT "addressbook_lists"."id", MIN("addressbook_lists"."container_id") AS "container_id", MIN("addressbook_lists"."name") AS "name", MIN("addressbook_lists"."description") AS "description", MIN("addressbook_lists"."email") AS "email", MIN("addressbook_lists"."type") AS "type", MIN("addressbook_lists"."list_type") AS "list_type", MIN("addressbook_lists"."created_by") AS "created_by", MIN("addressbook_lists"."creation_time") AS "creation_time", MIN("addressbook_lists"."last_modified_by") AS "last_modified_by", MIN("addressbook_lists"."last_modified_time") AS "last_modified_time", MIN("addressbook_lists"."is_deleted") AS "is_deleted", MIN("addressbook_lists"."deleted_by") AS "deleted_by", MIN("addressbook_lists"."deleted_time") AS "deleted_time", MIN("addressbook_lists"."seq") AS "seq", MIN((SELECT array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("email"))),',') FROM "tine20_addressbook" WHERE ("id" IN (SELECT "addressbook_list_members"."contact_id" FROM "tine20_addressbook_list_members" AS "addressbook_list_members" WHERE ("addressbook_list_members"."list_id" = "addressbook_lists"."id"))))) AS "emails", array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("addressbook_list_members"."contact_id"))),',') AS "members", MIN("groups"."id") AS "group_id" FROM "tine20_addressbook_lists" AS "addressbook_lists"
LEFT JOIN "tine20_addressbook_list_members" AS "addressbook_list_members" ON "addressbook_lists"."id" = "addressbook_list_members"."list_id"
LEFT JOIN "tine20_groups" AS "groups" ON "addressbook_lists"."id" = "groups"."list_id" WHERE ("addressbook_lists"."is_deleted" = 0) AND ("addressbook_lists"."id" IN ('919b9a87ad3e47925eaf9227cdeae39931e5bdf3', 'c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d', '73b7ec1e2200bded7d3e656fa7f1620c7e6b11b8', 'b359363d39912398e2cca2aed96f5be51b3d3305', '95d950362c16e07c5626cdf2a35453b8527a1528', '27a643c2bcda02c8e3bd05c93aec77e0d2c80eba')) AND ("addressbook_lists"."container_id" IN ('1') /* add acl in getMultiple /) GROUP BY "addressbook_lists"."id"
LOG: duration: 2900.978 ms execute pdo_stmt_000039ac: SELECT "addressbook_lists"."id", MIN("addressbook_lists"."container_id") AS "container_id", MIN("addressbook_lists"."name") AS "name", MIN("addressbook_lists"."description") AS "description", MIN("addressbook_lists"."email") AS "email", MIN("addressbook_lists"."type") AS "type", MIN("addressbook_lists"."list_type") AS "list_type", MIN("addressbook_lists"."created_by") AS "created_by", MIN("addressbook_lists"."creation_time") AS "creation_time", MIN("addressbook_lists"."last_modified_by") AS "last_modified_by", MIN("addressbook_lists"."last_modified_time") AS "last_modified_time", MIN("addressbook_lists"."is_deleted") AS "is_deleted", MIN("addressbook_lists"."deleted_by") AS "deleted_by", MIN("addressbook_lists"."deleted_time") AS "deleted_time", MIN("addressbook_lists"."seq") AS "seq", MIN((SELECT array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("email"))),',') FROM "tine20_addressbook" WHERE ("id" IN (SELECT "addressbook_list_members"."contact_id" FROM "tine20_addressbook_list_members" AS "addressbook_list_members" WHERE ("addressbook_list_members"."list_id" = "addressbook_lists"."id"))))) AS "emails", array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("addressbook_list_members"."contact_id"))),',') AS "members", MIN("groups"."id") AS "group_id" FROM "tine20_addressbook_lists" AS "addressbook_lists"
LEFT JOIN "tine20_addressbook_list_members" AS "addressbook_list_members" ON "addressbook_lists"."id" = "addressbook_list_members"."list_id"
LEFT JOIN "tine20_groups" AS "groups" ON "addressbook_lists"."id" = "groups"."list_id" WHERE ("addressbook_lists"."is_deleted" = 0) AND ("addressbook_lists"."id" IN ('919b9a87ad3e47925eaf9227cdeae39931e5bdf3', 'c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d', '73b7ec1e2200bded7d3e656fa7f1620c7e6b11b8', 'b359363d39912398e2cca2aed96f5be51b3d3305', '95d950362c16e07c5626cdf2a35453b8527a1528', '27a643c2bcda02c8e3bd05c93aec77e0d2c80eba')) AND ("addressbook_lists"."container_id" IN ('1') /
add acl in getMultiple /) GROUP BY "addressbook_lists"."id"
LOG: duration: 2846.553 ms execute pdo_stmt_000039cb: SELECT "addressbook_lists"."id", MIN("addressbook_lists"."container_id") AS "container_id", MIN("addressbook_lists"."name") AS "name", MIN("addressbook_lists"."description") AS "description", MIN("addressbook_lists"."email") AS "email", MIN("addressbook_lists"."type") AS "type", MIN("addressbook_lists"."list_type") AS "list_type", MIN("addressbook_lists"."created_by") AS "created_by", MIN("addressbook_lists"."creation_time") AS "creation_time", MIN("addressbook_lists"."last_modified_by") AS "last_modified_by", MIN("addressbook_lists"."last_modified_time") AS "last_modified_time", MIN("addressbook_lists"."is_deleted") AS "is_deleted", MIN("addressbook_lists"."deleted_by") AS "deleted_by", MIN("addressbook_lists"."deleted_time") AS "deleted_time", MIN("addressbook_lists"."seq") AS "seq", MIN((SELECT array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("email"))),',') FROM "tine20_addressbook" WHERE ("id" IN (SELECT "addressbook_list_members"."contact_id" FROM "tine20_addressbook_list_members" AS "addressbook_list_members" WHERE ("addressbook_list_members"."list_id" = "addressbook_lists"."id"))))) AS "emails", array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("addressbook_list_members"."contact_id"))),',') AS "members", MIN("groups"."id") AS "group_id" FROM "tine20_addressbook_lists" AS "addressbook_lists"
LEFT JOIN "tine20_addressbook_list_members" AS "addressbook_list_members" ON "addressbook_lists"."id" = "addressbook_list_members"."list_id"
LEFT JOIN "tine20_groups" AS "groups" ON "addressbook_lists"."id" = "groups"."list_id" WHERE ("addressbook_lists"."is_deleted" = 0) AND ("addressbook_lists"."id" IN ('919b9a87ad3e47925eaf9227cdeae39931e5bdf3', 'c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d', '73b7ec1e2200bded7d3e656fa7f1620c7e6b11b8', 'b359363d39912398e2cca2aed96f5be51b3d3305', '95d950362c16e07c5626cdf2a35453b8527a1528', '27a643c2bcda02c8e3bd05c93aec77e0d2c80eba')) AND ("addressbook_lists"."container_id" IN ('1') /
add acl in getMultiple /) GROUP BY "addressbook_lists"."id"
LOG: duration: 2851.564 ms execute pdo_stmt_000039dc: SELECT "addressbook_lists"."id", MIN("addressbook_lists"."container_id") AS "container_id", MIN("addressbook_lists"."name") AS "name", MIN("addressbook_lists"."description") AS "description", MIN("addressbook_lists"."email") AS "email", MIN("addressbook_lists"."type") AS "type", MIN("addressbook_lists"."list_type") AS "list_type", MIN("addressbook_lists"."created_by") AS "created_by", MIN("addressbook_lists"."creation_time") AS "creation_time", MIN("addressbook_lists"."last_modified_by") AS "last_modified_by", MIN("addressbook_lists"."last_modified_time") AS "last_modified_time", MIN("addressbook_lists"."is_deleted") AS "is_deleted", MIN("addressbook_lists"."deleted_by") AS "deleted_by", MIN("addressbook_lists"."deleted_time") AS "deleted_time", MIN("addressbook_lists"."seq") AS "seq", MIN((SELECT array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("email"))),',') FROM "tine20_addressbook" WHERE ("id" IN (SELECT "addressbook_list_members"."contact_id" FROM "tine20_addressbook_list_members" AS "addressbook_list_members" WHERE ("addressbook_list_members"."list_id" = "addressbook_lists"."id"))))) AS "emails", array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("addressbook_list_members"."contact_id"))),',') AS "members", MIN("groups"."id") AS "group_id" FROM "tine20_addressbook_lists" AS "addressbook_lists"
LEFT JOIN "tine20_addressbook_list_members" AS "addressbook_list_members" ON "addressbook_lists"."id" = "addressbook_list_members"."list_id"
LEFT JOIN "tine20_groups" AS "groups" ON "addressbook_lists"."id" = "groups"."list_id" WHERE ("addressbook_lists"."is_deleted" = 0) AND ("addressbook_lists"."id" IN ('754dbb3408a4a335c5539ad176b987291f6b2cf3', 'b359363d39912398e2cca2aed96f5be51b3d3305', '549f3500378bc5678e1efd734de6dd08a9638187', 'd2a05652954c6768cecfea4679e293a42b0e7bbc', 'fef6534562e28e90997202c2a443b4af8a82f3a9', '19dbcedf67391d1ea020a6cf9454ca0e89bf0aed', '6742543a2b1f3f817042b5573098c422213ca634', '77b21379dc9e4202b89d4c8f5b8210fd940401cf', 'c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d', '27a643c2bcda02c8e3bd05c93aec77e0d2c80eba', '61d0625b58a7c74ee176d7a009c21138428084fc')) AND ("addressbook_lists"."container_id" IN ('1') /
add acl in getMultiple /) GROUP BY "addressbook_lists"."id"
LOG: duration: 2858.494 ms execute pdo_stmt_000039e7: SELECT "addressbook_lists"."id", MIN("addressbook_lists"."container_id") AS "container_id", MIN("addressbook_lists"."name") AS "name", MIN("addressbook_lists"."description") AS "description", MIN("addressbook_lists"."email") AS "email", MIN("addressbook_lists"."type") AS "type", MIN("addressbook_lists"."list_type") AS "list_type", MIN("addressbook_lists"."created_by") AS "created_by", MIN("addressbook_lists"."creation_time") AS "creation_time", MIN("addressbook_lists"."last_modified_by") AS "last_modified_by", MIN("addressbook_lists"."last_modified_time") AS "last_modified_time", MIN("addressbook_lists"."is_deleted") AS "is_deleted", MIN("addressbook_lists"."deleted_by") AS "deleted_by", MIN("addressbook_lists"."deleted_time") AS "deleted_time", MIN("addressbook_lists"."seq") AS "seq", MIN((SELECT array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("email"))),',') FROM "tine20_addressbook" WHERE ("id" IN (SELECT "addressbook_list_members"."contact_id" FROM "tine20_addressbook_list_members" AS "addressbook_list_members" WHERE ("addressbook_list_members"."list_id" = "addressbook_lists"."id"))))) AS "emails", array_to_string(ARRAY(SELECT DISTINCT unnest(array_agg("addressbook_list_members"."contact_id"))),',') AS "members", MIN("groups"."id") AS "group_id" FROM "tine20_addressbook_lists" AS "addressbook_lists"
LEFT JOIN "tine20_addressbook_list_members" AS "addressbook_list_members" ON "addressbook_lists"."id" = "addressbook_list_members"."list_id"
LEFT JOIN "tine20_groups" AS "groups" ON "addressbook_lists"."id" = "groups"."list_id" WHERE ("addressbook_lists"."is_deleted" = 0) AND ("addressbook_lists"."id" IN ('754dbb3408a4a335c5539ad176b987291f6b2cf3', 'b359363d39912398e2cca2aed96f5be51b3d3305', '549f3500378bc5678e1efd734de6dd08a9638187', 'd2a05652954c6768cecfea4679e293a42b0e7bbc', 'fef6534562e28e90997202c2a443b4af8a82f3a9', '19dbcedf67391d1ea020a6cf9454ca0e89bf0aed', '6742543a2b1f3f817042b5573098c422213ca634', '77b21379dc9e4202b89d4c8f5b8210fd940401cf', 'c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d', '27a643c2bcda02c8e3bd05c93aec77e0d2c80eba', '61d0625b58a7c74ee176d7a009c21138428084fc')) AND ("addressbook_lists"."container_id" IN ('1') /
add acl in getMultiple */) GROUP BY "addressbook_lists"."id"

Here is the EXPLAIN ANALYZE output of PostgreSQL:

HashAggregate (cost=25542.51..25556.59 rows=11 width=609) (actual time=12110.574..12113.577 rows=11 loops=1)
Group Key: addressbook_lists.id
-> Hash Right Join (cost=39.88..186.47 rows=174 width=609) (actual time=0.940..10.111 rows=2208 loops=1)
Hash Cond: ((addressbook_list_members.list_id)::text = (addressbook_lists.id)::text)
-> Seq Scan on tine20_addressbook_list_members addressbook_list_members (cost=0.00..125.53 rows=5153 width=82) (actual time=0.005..3.074 rows=5153 loops=1)
-> Hash (cost=39.74..39.74 rows=11 width=568) (actual time=0.916..0.916 rows=11 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Hash Right Join (cost=17.57..39.74 rows=11 width=568) (actual time=0.369..0.904 rows=11 loops=1)
Hash Cond: ((groups.list_id)::text = (addressbook_lists.id)::text)
-> Seq Scan on tine20_groups groups (cost=0.00..20.14 rows=514 width=78) (actual time=0.006..0.227 rows=514 loops=1)
-> Hash (cost=17.43..17.43 rows=11 width=531) (actual time=0.319..0.319 rows=11 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on tine20_addressbook_lists addressbook_lists (cost=0.00..17.43 rows=11 width=531) (actual time=0.020..0.305 rows=11 loops=1)
Filter: ((is_deleted = '0'::numeric) AND ((container_id)::text = '1'::text) AND ((id)::text = ANY ('{754dbb3408a4a335c5539ad176b987291f6b2cf3,b359363d39912398e2cca2aed96f5be51b3d3305,549f3500378bc5678e1efd734de6dd08a9638187,d2a05652954c6768cecfea4679e293a42b0e7bbc,fef6534562e28e90997202c2a443b4af8a82f3a9,19dbcedf67391d1ea020a6cf9454ca0e89bf0aed,6742543a2b1f3f817042b5573098c422213ca634,77b21379dc9e4202b89d4c8f5b8210fd940401cf,c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d,27a643c2bcda02c8e3bd05c93aec77e0d2c80eba,61d0625b58a7c74ee176d7a009c21138428084fc}'::text[])))
Rows Removed by Filter: 317
SubPlan 2
-> Aggregate (cost=144.40..145.68 rows=1 width=17) (actual time=5.468..5.469 rows=1 loops=2208)
-> Hash Semi Join (cost=44.67..144.36 rows=16 width=17) (actual time=1.069..3.300 rows=752 loops=2208)
Hash Cond: ((tine20_addressbook.id)::text = (addressbook_list_members_1.contact_id)::text)
-> Seq Scan on tine20_addressbook (cost=0.00..94.80 rows=1680 width=57) (actual time=0.001..0.825 rows=1615 loops=2208)
-> Hash (cost=44.47..44.47 rows=16 width=41) (actual time=1.048..1.048 rows=752 loops=2208)
Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 94kB
-> Bitmap Heap Scan on tine20_addressbook_list_members addressbook_list_members_1 (cost=4.53..44.47 rows=16 width=41) (actual time=0.114..0.565 rows=752 loops=2208)
Recheck Cond: ((list_id)::text = (addressbook_lists.id)::text)
Heap Blocks: exact=125053
-> Bitmap Index Scan on tine20_addressbook_list_members_pkey (cost=0.00..4.53 rows=16 width=0) (actual time=0.106..0.106 rows=752 loops=2208)
Index Cond: ((list_id)::text = (addressbook_lists.id)::text)
SubPlan 1
-> HashAggregate (cost=0.76..1.26 rows=100 width=0) (actual time=0.914..1.233 rows=623 loops=2208)
Group Key: unnest(array_agg(tine20_addressbook.email))
-> Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.001..0.380 rows=752 loops=2208)
SubPlan 3
-> HashAggregate (cost=0.76..1.26 rows=100 width=0) (actual time=0.255..0.340 rows=201 loops=11)
Group Key: unnest(array_agg(addressbook_list_members.contact_id))
-> Result (cost=0.00..0.51 rows=100 width=0) (actual time=0.001..0.103 rows=201 loops=11)
Planning time: 1.777 ms
Execution time: 12113.833 ms
(37 rows)

Needless to say that syncing the contacts are failing now due to timeouts. What the f.... ist going on here?

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by nbe-renzel-net on 28 Jan 2018 15:43

Well, my PostgreSQL dumps, which I'm doing every two hours, have become 140MB larger each. Resulting in a 3GB dump now. Thank you.

I've reverted to 2017.08.3

Gloirin commented Jun 11, 2018

Comment posted by nbe-renzel-net on 28 Jan 2018 15:43

Well, my PostgreSQL dumps, which I'm doing every two hours, have become 140MB larger each. Resulting in a 3GB dump now. Thank you.

I've reverted to 2017.08.3

@Gloirin

This comment has been minimized.

Show comment
Hide comment
@Gloirin

Gloirin Jun 11, 2018

Comment posted by pschuele on 15 Mar 2018 13:42

do you still have this problem with the current release (2018.02.2)?

Gloirin commented Jun 11, 2018

Comment posted by pschuele on 15 Mar 2018 13:42

do you still have this problem with the current release (2018.02.2)?

@nbe-renzel-net

This comment has been minimized.

Show comment
Hide comment
@nbe-renzel-net

nbe-renzel-net Jun 14, 2018

Okay, I've upgraded to 2018.02.06 and unfortunately, the problem still exists. I've tried to switch to "string_agg" in "Tinebase/Backend/Sql/Command/Pgsql.php" line 42 and adding a type cast:

    // since 9.0
    return new Zend_Db_Expr("string_agg(DISTINCT ${quotedField}::text, ',')"); 

but no success:

tine.dev=> EXPLAIN ANALYZE SELECT "addressbook_lists"."id",
min("addressbook_lists"."container_id") AS "container_id",
min("addressbook_lists"."name") AS "name",
min("addressbook_lists"."description") AS "description",
min("addressbook_lists"."email") AS "email",
min("addressbook_lists"."type") AS "type",
min("addressbook_lists"."list_type") AS "list_type",
min("addressbook_lists"."created_by") AS "created_by",
min("addressbook_lists"."creation_time") AS "creation_time",
min("addressbook_lists"."last_modified_by") AS "last_modified_by",
min("addressbook_lists"."last_modified_time") AS "last_modified_time",
min("addressbook_lists"."is_deleted") AS "is_deleted",
min("addressbook_lists"."deleted_by") AS "deleted_by",
min("addressbook_lists"."deleted_time") AS "deleted_time",
min("addressbook_lists"."seq") AS "seq",
min(
(SELECT string_agg(DISTINCT "email"::text, ',')
FROM "tine20_addressbook"
WHERE ("id" IN
(SELECT "addressbook_list_members"."contact_id"
FROM "tine20_addressbook_list_members" AS "addressbook_list_members"
WHERE ("addressbook_list_members"."list_id" = "addressbook_lists"."id"))))) AS "emails",
string_agg(DISTINCT "addressbook_list_members"."contact_id"::text, ',') AS "members",
min("groups"."id") AS "group_id"
FROM "tine20_addressbook_lists" AS "addressbook_lists"
LEFT JOIN "tine20_addressbook_list_members" AS "addressbook_list_members" ON "addressbook_lists"."id" = "addressbook_list_members"."list_id"
LEFT JOIN "tine20_groups" AS "groups" ON "addressbook_lists"."id" = "groups"."list_id"
WHERE ("addressbook_lists"."is_deleted" = 0)
AND ("addressbook_lists"."id" IN ('da30371a1ce2fd95b35f6e609549ce95af02235d',
'c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d',
'97bfb06e783be1bb40d41736053dacff909df3b0',
'b359363d39912398e2cca2aed96f5be51b3d3305',
'85a750ac7e2e1a058c9b15fbae5f45d2bf48c0d8',
'0301cbf49af1939f2cec08caaf5f9d1512ba819c',
'27a643c2bcda02c8e3bd05c93aec77e0d2c80eba',
'0f25cc2d2d835bbad0a61ebf51f16c31b1805766',
'64074dd83940cfd035f2a79c2d99c33d651043bb'))
AND ("addressbook_lists"."container_id" IN ('1')/* add acl in getMultiple */)
GROUP BY "addressbook_lists"."id";
QUERY PLAN
GroupAggregate (cost=204.51..24354.98 rows=9 width=608) (actual time=40.418..15390.017 rows=9 loops=1)
Group Key: addressbook_lists.id
-> Sort (cost=204.51..204.89 rows=152 width=608) (actual time=14.970..16.773 rows=2396 loops=1)
Sort Key: addressbook_lists.id
Sort Method: quicksort Memory: 1032kB
-> Hash Right Join (cost=39.23..199.00 rows=152 width=608) (actual time=1.102..11.793 rows=2396 loops=1)
Hash Cond: ((addressbook_list_members.list_id)::text = (addressbook_lists.id)::text)
-> Seq Scan on tine20_addressbook_list_members addressbook_list_members (cost=0.00..137.18 rows=5618 width=82) (actual time=0.002..3.987 rows=5618 loops=1)
-> Hash (cost=39.12..39.12 rows=9 width=567) (actual time=1.079..1.079 rows=9 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Hash Right Join (cost=16.85..39.12 rows=9 width=567) (actual time=0.397..1.066 rows=9 loops=1)
Hash Cond: ((groups.list_id)::text = (addressbook_lists.id)::text)
-> Seq Scan on tine20_groups groups (cost=0.00..20.22 rows=522 width=78) (actual time=0.002..0.375 rows=522 loops=1)
-> Hash (cost=16.74..16.74 rows=9 width=530) (actual time=0.277..0.277 rows=9 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on tine20_addressbook_lists addressbook_lists (cost=0.00..16.74 rows=9 width=530) (actual time=0.013..0.267 rows=9 loops=1)
Filter: ((is_deleted = '0'::numeric) AND ((container_id)::text = '1'::text) AND ((id)::text = ANY ('{da30371a1ce2fd95b35f6e609549ce95af02235d,c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d,97bfb06e783be1bb40d41736053dacff909df3b0,b359363d39912398e2cca2aed96f5be51b3d3305,85a750ac7e2e1a058c9b15fbae5f45d2bf48c0d8,0301cbf49af1939f2cec08caaf5f9d1512ba819c,27a643c2bcda02c8e3bd05c93aec77e0d2c80eba,0f25cc2d2d835bbad0a61ebf51f16c31b1805766,64074dd83940cfd035f2a79c2d99c33d651043bb}'::text[])))
Rows Removed by Filter: 324
SubPlan 1
-> Aggregate (cost=158.83..158.84 rows=1 width=17) (actual time=6.404..6.405 rows=1 loops=2396)
-> Hash Semi Join (cost=47.63..158.78 rows=17 width=17) (actual time=1.562..5.120 rows=843 loops=2396)
Hash Cond: ((tine20_addressbook.id)::text = (addressbook_list_members_1.contact_id)::text)
-> Seq Scan on tine20_addressbook (cost=0.00..105.71 rows=1871 width=57) (actual time=0.002..1.332 rows=1871 loops=2396)
-> Hash (cost=47.42..47.42 rows=17 width=41) (actual time=1.525..1.525 rows=843 loops=2396)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Bitmap Heap Scan on tine20_addressbook_list_members addressbook_list_members_1 (cost=4.54..47.42 rows=17 width=41) (actual time=0.106..0.826 rows=843 loops=2396)
Recheck Cond: ((list_id)::text = (addressbook_lists.id)::text)
Heap Blocks: exact=152202
-> Bitmap Index Scan on tine20_addressbook_list_members_pkey (cost=0.00..4.54 rows=17 width=0) (actual time=0.098..0.098 rows=843 loops=2396)
Index Cond: ((list_id)::text = (addressbook_lists.id)::text)
Planning time: 0.669 ms
Execution time: 15390.531 ms
(32 rows)

nbe-renzel-net commented Jun 14, 2018

Okay, I've upgraded to 2018.02.06 and unfortunately, the problem still exists. I've tried to switch to "string_agg" in "Tinebase/Backend/Sql/Command/Pgsql.php" line 42 and adding a type cast:

    // since 9.0
    return new Zend_Db_Expr("string_agg(DISTINCT ${quotedField}::text, ',')"); 

but no success:

tine.dev=> EXPLAIN ANALYZE SELECT "addressbook_lists"."id",
min("addressbook_lists"."container_id") AS "container_id",
min("addressbook_lists"."name") AS "name",
min("addressbook_lists"."description") AS "description",
min("addressbook_lists"."email") AS "email",
min("addressbook_lists"."type") AS "type",
min("addressbook_lists"."list_type") AS "list_type",
min("addressbook_lists"."created_by") AS "created_by",
min("addressbook_lists"."creation_time") AS "creation_time",
min("addressbook_lists"."last_modified_by") AS "last_modified_by",
min("addressbook_lists"."last_modified_time") AS "last_modified_time",
min("addressbook_lists"."is_deleted") AS "is_deleted",
min("addressbook_lists"."deleted_by") AS "deleted_by",
min("addressbook_lists"."deleted_time") AS "deleted_time",
min("addressbook_lists"."seq") AS "seq",
min(
(SELECT string_agg(DISTINCT "email"::text, ',')
FROM "tine20_addressbook"
WHERE ("id" IN
(SELECT "addressbook_list_members"."contact_id"
FROM "tine20_addressbook_list_members" AS "addressbook_list_members"
WHERE ("addressbook_list_members"."list_id" = "addressbook_lists"."id"))))) AS "emails",
string_agg(DISTINCT "addressbook_list_members"."contact_id"::text, ',') AS "members",
min("groups"."id") AS "group_id"
FROM "tine20_addressbook_lists" AS "addressbook_lists"
LEFT JOIN "tine20_addressbook_list_members" AS "addressbook_list_members" ON "addressbook_lists"."id" = "addressbook_list_members"."list_id"
LEFT JOIN "tine20_groups" AS "groups" ON "addressbook_lists"."id" = "groups"."list_id"
WHERE ("addressbook_lists"."is_deleted" = 0)
AND ("addressbook_lists"."id" IN ('da30371a1ce2fd95b35f6e609549ce95af02235d',
'c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d',
'97bfb06e783be1bb40d41736053dacff909df3b0',
'b359363d39912398e2cca2aed96f5be51b3d3305',
'85a750ac7e2e1a058c9b15fbae5f45d2bf48c0d8',
'0301cbf49af1939f2cec08caaf5f9d1512ba819c',
'27a643c2bcda02c8e3bd05c93aec77e0d2c80eba',
'0f25cc2d2d835bbad0a61ebf51f16c31b1805766',
'64074dd83940cfd035f2a79c2d99c33d651043bb'))
AND ("addressbook_lists"."container_id" IN ('1')/* add acl in getMultiple */)
GROUP BY "addressbook_lists"."id";
QUERY PLAN
GroupAggregate (cost=204.51..24354.98 rows=9 width=608) (actual time=40.418..15390.017 rows=9 loops=1)
Group Key: addressbook_lists.id
-> Sort (cost=204.51..204.89 rows=152 width=608) (actual time=14.970..16.773 rows=2396 loops=1)
Sort Key: addressbook_lists.id
Sort Method: quicksort Memory: 1032kB
-> Hash Right Join (cost=39.23..199.00 rows=152 width=608) (actual time=1.102..11.793 rows=2396 loops=1)
Hash Cond: ((addressbook_list_members.list_id)::text = (addressbook_lists.id)::text)
-> Seq Scan on tine20_addressbook_list_members addressbook_list_members (cost=0.00..137.18 rows=5618 width=82) (actual time=0.002..3.987 rows=5618 loops=1)
-> Hash (cost=39.12..39.12 rows=9 width=567) (actual time=1.079..1.079 rows=9 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 11kB
-> Hash Right Join (cost=16.85..39.12 rows=9 width=567) (actual time=0.397..1.066 rows=9 loops=1)
Hash Cond: ((groups.list_id)::text = (addressbook_lists.id)::text)
-> Seq Scan on tine20_groups groups (cost=0.00..20.22 rows=522 width=78) (actual time=0.002..0.375 rows=522 loops=1)
-> Hash (cost=16.74..16.74 rows=9 width=530) (actual time=0.277..0.277 rows=9 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Seq Scan on tine20_addressbook_lists addressbook_lists (cost=0.00..16.74 rows=9 width=530) (actual time=0.013..0.267 rows=9 loops=1)
Filter: ((is_deleted = '0'::numeric) AND ((container_id)::text = '1'::text) AND ((id)::text = ANY ('{da30371a1ce2fd95b35f6e609549ce95af02235d,c308b2fc7fcdf66fda1d10cca31fc15029fc4d0d,97bfb06e783be1bb40d41736053dacff909df3b0,b359363d39912398e2cca2aed96f5be51b3d3305,85a750ac7e2e1a058c9b15fbae5f45d2bf48c0d8,0301cbf49af1939f2cec08caaf5f9d1512ba819c,27a643c2bcda02c8e3bd05c93aec77e0d2c80eba,0f25cc2d2d835bbad0a61ebf51f16c31b1805766,64074dd83940cfd035f2a79c2d99c33d651043bb}'::text[])))
Rows Removed by Filter: 324
SubPlan 1
-> Aggregate (cost=158.83..158.84 rows=1 width=17) (actual time=6.404..6.405 rows=1 loops=2396)
-> Hash Semi Join (cost=47.63..158.78 rows=17 width=17) (actual time=1.562..5.120 rows=843 loops=2396)
Hash Cond: ((tine20_addressbook.id)::text = (addressbook_list_members_1.contact_id)::text)
-> Seq Scan on tine20_addressbook (cost=0.00..105.71 rows=1871 width=57) (actual time=0.002..1.332 rows=1871 loops=2396)
-> Hash (cost=47.42..47.42 rows=17 width=41) (actual time=1.525..1.525 rows=843 loops=2396)
Buckets: 1024 Batches: 1 Memory Usage: 10kB
-> Bitmap Heap Scan on tine20_addressbook_list_members addressbook_list_members_1 (cost=4.54..47.42 rows=17 width=41) (actual time=0.106..0.826 rows=843 loops=2396)
Recheck Cond: ((list_id)::text = (addressbook_lists.id)::text)
Heap Blocks: exact=152202
-> Bitmap Index Scan on tine20_addressbook_list_members_pkey (cost=0.00..4.54 rows=17 width=0) (actual time=0.098..0.098 rows=843 loops=2396)
Index Cond: ((list_id)::text = (addressbook_lists.id)::text)
Planning time: 0.669 ms
Execution time: 15390.531 ms
(32 rows)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment