{"action":"created","issue":{"url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1462","repository_url":"https://api.github.com/repos/greenplum-db/gpdb","labels_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1462/labels{/name}","comments_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1462/comments","events_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1462/events","html_url":"https://github.com/greenplum-db/gpdb/pull/1462","id":196559331,"number":1462,"title":"Removing redundant partitions during a join partition elimination","user":{"login":"foyzur","id":1253020,"avatar_url":"https://avatars.githubusercontent.com/u/1253020?v=3","gravatar_id":"","url":"https://api.github.com/users/foyzur","html_url":"https://github.com/foyzur","followers_url":"https://api.github.com/users/foyzur/followers","following_url":"https://api.github.com/users/foyzur/following{/other_user}","gists_url":"https://api.github.com/users/foyzur/gists{/gist_id}","starred_url":"https://api.github.com/users/foyzur/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/foyzur/subscriptions","organizations_url":"https://api.github.com/users/foyzur/orgs","repos_url":"https://api.github.com/users/foyzur/repos","events_url":"https://api.github.com/users/foyzur/events{/privacy}","received_events_url":"https://api.github.com/users/foyzur/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":9,"created_at":"2016-12-20T00:56:34Z","updated_at":"2017-01-04T00:13:49Z","closed_at":null,"pull_request":{"url":"https://api.github.com/repos/greenplum-db/gpdb/pulls/1462","html_url":"https://github.com/greenplum-db/gpdb/pull/1462","diff_url":"https://github.com/greenplum-db/gpdb/pull/1462.diff","patch_url":"https://github.com/greenplum-db/gpdb/pull/1462.patch"},"body":"Prior to this PR PartitionSelector only appended list of partitions to scan in the existing list of partitions, without ever removing parts. For a join partition elimination (where partitions vary depending on the outer tuple's value) we kept scanning redundant partitions based on prior values of outer tuples. E.g., in a repro like this:\r\n\r\n```\r\ndrop table if exists part;\r\ncreate table part(a int, b int)\r\n  with (appendonly=true, orientation=row)\r\n  distributed by (b)\r\n  partition by range (a)\r\n  (\r\n      PARTITION pfirst  END(1) INCLUSIVE,\r\n      PARTITION pinter  START(1) EXCLUSIVE END (2) INCLUSIVE,\r\n      PARTITION plast   START (2) EXCLUSIVE\r\n  );\r\n\r\n--First partition\r\ninsert into part values(1, 1);\r\n\r\n--Second partition\r\ninsert into part values(2, 1);\r\n\r\n--Third partition\r\ninsert into part values(3, 1);\r\n\r\ndrop table if exists whole;\r\n-- Unpartitioned\r\ncreate table whole(a int, b int)\r\n  with (appendonly=true, orientation=row)\r\n  distributed by (b);\r\n\r\ninsert into whole values(1, 1);\r\ninsert into whole values(2, 1);\r\ninsert into whole values(3, 1);\r\n\r\n\r\nset optimizer=on;\r\nset optimizer_enable_broadcast_nestloop_outer_child = on;\r\nset optimizer_enumerate_plans=true;\r\nset client_min_messages='log';\r\nset optimizer_plan_id=2;\r\nset optimizer_partition_selection_log=on;\r\n\r\nselect part.a, whole.a from part join whole on part.a >= whole.a;\r\n```\r\n\r\nThe first outer tuple would be a=\"1\" and therefore, the partition selector will scan all the partitions on the \"part\" table (everything on part table is greater or equal to 1 for column a). Successively, when a = 2 comes from the outer side, the partition selector would produce only two parts on the inner side. But, because it never removed any parts, the list of selected parts will still remain all three parts. For the final iteration of a=3 from outer side, only 1 part of the inner will qualify. Even for that the prior implementation will keep scanning all three parts.\r\n\r\nThe above repro produces a plan like:\r\n\r\n```\r\nfrahman=# explain select part.a, whole.a from part join whole on part.a >= whole.a;\r\nLOG:  statement: explain select part.a, whole.a from part join whole on part.a >= whole.a;\r\nLOG:  2016-12-19 16:59:25:845975 PST,THD000,TRACE,\"[OPT]: Number of plan alternatives: 7\r\n\",\r\n2016-12-19 16:59:25:846292 PST,THD000,TRACE,\"[OPT]: Successfully generated plan: 2\r\n\",\r\n                                             QUERY PLAN\r\n----------------------------------------------------------------------------------------------------\r\n Gather Motion 2:1  (slice2; segments: 2)  (cost=0.00..1324032.36 rows=1 width=8)\r\n   ->  Nested Loop  (cost=0.00..1324032.36 rows=1 width=8)\r\n         Join Filter: part.a >= whole.a\r\n         ->  Partition Selector for part (dynamic scan id: 1)  (cost=10.00..100.00 rows=50 width=4)\r\n               Filter: part.a >= part.a\r\n               ->  Broadcast Motion 2:2  (slice1; segments: 2)  (cost=0.00..431.00 rows=1 width=4)\r\n                     ->  Table Scan on whole  (cost=0.00..431.00 rows=1 width=4)\r\n         ->  Dynamic Table Scan on part (dynamic scan id: 1)  (cost=0.00..431.00 rows=1 width=4)\r\n Settings:  optimizer=on\r\n Optimizer status: PQO version 1.695\r\n(10 rows)\r\n```\r\n\r\nThe resulting output from above repro looks like:\r\n\r\n```\r\nfrahman=# select part.a, whole.a from part join whole on part.a >= whole.a;\r\nLOG:  statement: select part.a, whole.a from part join whole on part.a >= whole.a;\r\nLOG:  2016-12-19 15:17:03:500030 PST,THD000,TRACE,\"[OPT]: Number of plan alternatives: 7\r\n\",\r\n2016-12-19 15:17:03:500512 PST,THD000,TRACE,\"[OPT]: Successfully generated plan: 2\r\n\",\r\nLOG:  DPE matched partitions: {}  (seg0 slice2 127.0.0.1:25432 pid=24868)\r\nLOG:  DPE matched partitions: {part_1_prt_plast, part_1_prt_pfirst, part_1_prt_pinter, }  (seg0 slice2 127.0.0.1:25432 pid=24868)\r\nLOG:  DPE matched partitions: {part_1_prt_plast, part_1_prt_pfirst, part_1_prt_pinter, }  (seg0 slice2 127.0.0.1:25432 pid=24868)\r\nLOG:  DPE matched partitions: {part_1_prt_plast, part_1_prt_pfirst, part_1_prt_pinter, }  (seg0 slice2 127.0.0.1:25432 pid=24868)\r\n a | a\r\n---+---\r\n 3 | 1\r\n 1 | 1\r\n 2 | 1\r\n 3 | 2\r\n 2 | 2\r\n 3 | 3\r\n(6 rows)\r\n```\r\n\r\nWith the fix of this PR the output looks like:\r\n\r\n```\r\nfrahman=# select part.a, whole.a from part join whole on part.a >= whole.a;\r\nLOG:  statement: select part.a, whole.a from part join whole on part.a >= whole.a;\r\nLOG:  2016-12-19 16:58:15:259185 PST,THD000,TRACE,\"[OPT]: Number of plan alternatives: 7\r\n\",\r\n2016-12-19 16:58:15:259521 PST,THD000,TRACE,\"[OPT]: Successfully generated plan: 2\r\n\",\r\nLOG:  DPE matched partitions: {}  (seg0 slice2 127.0.0.1:25432 pid=72939)\r\nLOG:  DPE matched partitions: {part_1_prt_plast, part_1_prt_pfirst, part_1_prt_pinter, }  (seg0 slice2 127.0.0.1:25432 pid=72939)\r\nLOG:  DPE matched partitions: {part_1_prt_plast, part_1_prt_pinter, }  (seg0 slice2 127.0.0.1:25432 pid=72939)\r\nLOG:  DPE matched partitions: {part_1_prt_plast, }  (seg0 slice2 127.0.0.1:25432 pid=72939)\r\n```\r\n\r\nThis PR undoes previous part selection by saving the previous run's selected parts. A simple context reset doesn't work as other partition selectors might be sharing the selection process. The PartitionSelector is designed to be a \"voting\" based mechanism where multiple partition selectors can feed into one scan operator's set of chosen parts using \"votes\". The DTS will eventually figure out if all partition selectors voted yes, and then it considers such parts as selected. The selected partitions are saved as PartOidEntry in a hash table which has selectorList that contains all the \"voters\" PartitionSelector. Therefore, just because one PartitionSelector decides not to chose, doesn't mean we can throw such an entry using simple memory context. Because of this complexity, this PR undoes the previous selector for each PartitionSelector."},"comment":{"url":"https://api.github.com/repos/greenplum-db/gpdb/issues/comments/270262412","html_url":"https://github.com/greenplum-db/gpdb/pull/1462#issuecomment-270262412","issue_url":"https://api.github.com/repos/greenplum-db/gpdb/issues/1462","id":270262412,"user":{"login":"foyzur","id":1253020,"avatar_url":"https://avatars.githubusercontent.com/u/1253020?v=3","gravatar_id":"","url":"https://api.github.com/users/foyzur","html_url":"https://github.com/foyzur","followers_url":"https://api.github.com/users/foyzur/followers","following_url":"https://api.github.com/users/foyzur/following{/other_user}","gists_url":"https://api.github.com/users/foyzur/gists{/gist_id}","starred_url":"https://api.github.com/users/foyzur/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/foyzur/subscriptions","organizations_url":"https://api.github.com/users/foyzur/orgs","repos_url":"https://api.github.com/users/foyzur/repos","events_url":"https://api.github.com/users/foyzur/events{/privacy}","received_events_url":"https://api.github.com/users/foyzur/received_events","type":"User","site_admin":false},"created_at":"2017-01-04T00:13:48Z","updated_at":"2017-01-04T00:13:48Z","body":"@armenatzoglou looks like the @xinzweb repro uses \"set optimizer_plan_id=229\" which is not suitable as an automated test. I confirmed that with @vraghavan78. I also ensured that the PR pipeline is green. I am merging the PR. Please let me know if you have any concern and I will be happy to revert and address."}}
{"actor":{"display_login":"foyzur"}}