{"action":"opened","issue":{"url":"https://api.github.com/repos/greenplum-db/gpdb/issues/5849","repository_url":"https://api.github.com/repos/greenplum-db/gpdb","labels_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/5849/labels{/name}","comments_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/5849/comments","events_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/5849/events","html_url":"https://github.com/greenplum-db/gpdb/issues/5849","id":363351524,"node_id":"MDU6SXNzdWUzNjMzNTE1MjQ=","number":5849,"title":"Incorrect slice memory usage reported in EXPLAIN ANALYZE ","user":{"login":"hardikar","id":2496677,"node_id":"MDQ6VXNlcjI0OTY2Nzc=","avatar_url":"https://avatars2.githubusercontent.com/u/2496677?v=4","gravatar_id":"","url":"https://api.github.com/users/hardikar","html_url":"https://github.com/hardikar","followers_url":"https://api.github.com/users/hardikar/followers","following_url":"https://api.github.com/users/hardikar/following{/other_user}","gists_url":"https://api.github.com/users/hardikar/gists{/gist_id}","starred_url":"https://api.github.com/users/hardikar/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/hardikar/subscriptions","organizations_url":"https://api.github.com/users/hardikar/orgs","repos_url":"https://api.github.com/users/hardikar/repos","events_url":"https://api.github.com/users/hardikar/events{/privacy}","received_events_url":"https://api.github.com/users/hardikar/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-09-24T23:02:59Z","updated_at":"2018-09-24T23:02:59Z","closed_at":null,"author_association":"MEMBER","body":"GPDB version: 5.11\r\n\r\n```\r\nCREATE TABLE foo (a int, b int, c int);\r\nCREATE TABLE bar (a int, b int, c int);\r\nset optimizer = on;\r\nset statement_mem = \"20MB\";\r\n```\r\n\r\n```\r\nshardikar=# EXPLAIN ANALYZE select * from (select a from foo UNION select b from foo) t(c), bar where t.c = bar.c;                                                                                                                                                                                                                                                                                        QUERY PLAN\r\n----------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n Gather Motion 3:1  (slice3; segments: 3)  (cost=0.00..1293.00 rows=1 width=16)\r\n   Rows out:  0 rows at destination with 3657 ms to end.\r\n   ->  Hash Join  (cost=0.00..1293.00 rows=1 width=16)\r\n         Hash Cond: bar.c = public.foo.a\r\n         Rows out:  0 rows (seg0) with 3656 ms to end.\r\n         Executor memory:  7813K bytes avg, 7814K bytes max (seg2).\r\n         Work_mem used:  7813K bytes avg, 7814K bytes max (seg2). Workfile: (0 spilling)\r\n         ->  Redistribute Motion 3:3  (slice1; segments: 3)  (cost=0.00..431.00 rows=1 width=12)\r\n               Hash Key: bar.c\r\n               Rows out:  0 rows at destination (seg0) with 0.007 ms to end.\r\n               ->  Table Scan on bar  (cost=0.00..431.00 rows=1 width=12)\r\n                     Rows out:  0 rows (seg0) with 0.046 ms to end.\r\n         ->  Hash  (cost=862.00..862.00 rows=1 width=4)\r\n               Rows in:  Avg 333333.3 rows x 3 workers.  Max 333385 rows (seg2) with 3604 ms to end, start offset by 0.827 ms.\r\n               ->  GroupAggregate  (cost=0.00..862.00 rows=1 width=4)\r\n                     Group By: public.foo.a\r\n                     Rows out:  Avg 333333.3 rows x 3 workers.  Max 333385 rows (seg2) with 3210 ms to first row, 3488 ms to end.\r\n                     ->  Sort  (cost=0.00..862.00 rows=1 width=4)\r\n                           Sort Key: public.foo.a\r\n                           Sort Method:  external sort  Max Disk: 2624KB  Avg Disk: 2624KB (3 segments)\r\n                           Rows out:  Avg 333333.3 rows x 3 workers.  Max 333385 rows (seg2) with 3210 ms to first row, 3299 ms to end.\r\n                           Executor memory:  10493K bytes avg, 10493K bytes max (seg0).\r\n                           Work_mem used:  10493K bytes avg, 10493K bytes max (seg0). Workfile: (3 spilling)\r\n                           Work_mem wanted: 40501K bytes avg, 40507K bytes max (seg2) to lessen workfile I/O affecting 3 workers.\r\n                           ->  Redistribute Motion 3:3  (slice2; segments: 3)  (cost=0.00..862.00 rows=1 width=4)\r\n                                 Hash Key: public.foo.a\r\n                                 Rows out:  Avg 333333.3 rows x 3 workers at destination.  Max 333385 rows (seg2) with 1824 ms to first row, 2501 ms to end.\r\n                                 ->  GroupAggregate  (cost=0.00..862.00 rows=1 width=4)\r\n                                       Group By: public.foo.a\r\n                                       Rows out:  Avg 333333.3 rows x 3 workers.  Max 333385 rows (seg2) with 1820 ms to first row, 2795 ms to end.\r\n                                       ->  Sort  (cost=0.00..862.00 rows=1 width=4)\r\n                                             Sort Key: public.foo.a\r\n                                             Sort Method:  external merge  Max Disk: 5248KB  Avg Disk: 5248KB (3 segments)\r\n                                             Rows out:  Avg 666666.7 rows x 3 workers.  Max 666770 rows (seg2) with 1820 ms to first row, 2418 ms to end.\r\n                                             Executor memory:  10493K bytes avg, 10493K bytes max (seg0).\r\n                                             Work_mem used:  10493K bytes avg, 10493K bytes max (seg0). Workfile: (3 spilling)\r\n                                             Work_mem wanted: 81001K bytes avg, 81014K bytes max (seg2) to lessen workfile I/O affecting 3 workers.\r\n                                             ->  Append  (cost=0.00..862.00 rows=1 width=4)\r\n                                                   Rows out:  Avg 666666.7 rows x 3 workers.  Max 666770 rows (seg2) with 0.057 ms to first row, 665 ms to end.\r\n                                                   ->  Table Scan on foo  (cost=0.00..431.00 rows=1 width=4)\r\n                                                         Rows out:  Avg 333333.3 rows x 3 workers.  Max 333385 rows (seg2) with 0.056 ms to first row, 291 ms to end.\r\n                                                   ->  Table Scan on foo  (cost=0.00..431.00 rows=1 width=4)\r\n                                                         Rows out:  Avg 333333.3 rows x 3 workers.  Max 333385 rows (seg2) with 0.033 ms to first row, 104 ms to end.\r\n Slice statistics:\r\n   (slice0)    Executor memory: 386K bytes.\r\n   (slice1)    Executor memory: 183K bytes avg x 3 workers, 183K bytes max (seg0).\r\n   (slice2)  * Executor memory: 10704K bytes avg x 3 workers, 10704K bytes max (seg0).  Work_mem: 10493K bytes max, 81014K bytes wanted.\r\n   (slice3)  * Executor memory: 44180K bytes avg x 3 workers, 44180K bytes max (seg0).  Work_mem: 10493K bytes max, 40507K bytes wanted.\r\n Statement statistics:\r\n   Memory used: 20480K bytes\r\n   Memory wanted: 244039K bytes\r\n Settings:  optimizer=on\r\n Optimizer status: PQO version 2.73.0\r\n Total runtime: 3663.568 ms\r\n(54 rows)\r\n```\r\n\r\nNotice the \"Slice statistics\". Slice 3 apparently uses ~44 MB when the statement_mem is set to 20 MB. But looking at the nodes in slice 3, it looks like it actually only takes ~10 MB. It seems EXPLAIN ANALYZE is reporting this number incorrectly."}}
{"actor":{"display_login":"hardikar"}}