{"action":"created","issue":{"url":"https://api.github.com/repos/HazyResearch/deepdive/issues/378","labels_url":"https://api.github.com/repos/HazyResearch/deepdive/issues/378/labels{/name}","comments_url":"https://api.github.com/repos/HazyResearch/deepdive/issues/378/comments","events_url":"https://api.github.com/repos/HazyResearch/deepdive/issues/378/events","html_url":"https://github.com/HazyResearch/deepdive/issues/378","id":105110287,"number":378,"title":"PGXL failing on large datasets","user":{"login":"raphaelhoffmann","id":991789,"avatar_url":"https://avatars.githubusercontent.com/u/991789?v=3","gravatar_id":"","url":"https://api.github.com/users/raphaelhoffmann","html_url":"https://github.com/raphaelhoffmann","followers_url":"https://api.github.com/users/raphaelhoffmann/followers","following_url":"https://api.github.com/users/raphaelhoffmann/following{/other_user}","gists_url":"https://api.github.com/users/raphaelhoffmann/gists{/gist_id}","starred_url":"https://api.github.com/users/raphaelhoffmann/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/raphaelhoffmann/subscriptions","organizations_url":"https://api.github.com/users/raphaelhoffmann/orgs","repos_url":"https://api.github.com/users/raphaelhoffmann/repos","events_url":"https://api.github.com/users/raphaelhoffmann/events{/privacy}","received_events_url":"https://api.github.com/users/raphaelhoffmann/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":4,"created_at":"2015-09-06T18:50:24Z","updated_at":"2015-09-07T01:11:00Z","closed_at":null,"body":"Trying to run one of our memex extractors, PGXL fails on the following query:\r\n\r\nSELECT DISTINCT t0.id AS factor_id, t1.id AS weight_id,  \"ismassageparlorad.id\"\r\n  FROM dd_query_f_ismassageparlorad_features t0, dd_weights_f_ismassageparlorad_features t1\r\n  WHERE  t0.\"ismassageparlorad.feature\" = t1.\"ismassageparlorad.feature\"\r\n\r\nA few observations:\r\n- It seems DISTINCT is redundant and can be dropped (?)\r\n- I added an index on the two feature columns, but that didn't help\r\n- feature is not a distribution column – we can try to redistribute the table (but shouldn't that happen automatically, when trying to join?)"},"comment":{"url":"https://api.github.com/repos/HazyResearch/deepdive/issues/comments/138149520","html_url":"https://github.com/HazyResearch/deepdive/issues/378#issuecomment-138149520","issue_url":"https://api.github.com/repos/HazyResearch/deepdive/issues/378","id":138149520,"user":{"login":"raphaelhoffmann","id":991789,"avatar_url":"https://avatars.githubusercontent.com/u/991789?v=3","gravatar_id":"","url":"https://api.github.com/users/raphaelhoffmann","html_url":"https://github.com/raphaelhoffmann","followers_url":"https://api.github.com/users/raphaelhoffmann/followers","following_url":"https://api.github.com/users/raphaelhoffmann/following{/other_user}","gists_url":"https://api.github.com/users/raphaelhoffmann/gists{/gist_id}","starred_url":"https://api.github.com/users/raphaelhoffmann/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/raphaelhoffmann/subscriptions","organizations_url":"https://api.github.com/users/raphaelhoffmann/orgs","repos_url":"https://api.github.com/users/raphaelhoffmann/repos","events_url":"https://api.github.com/users/raphaelhoffmann/events{/privacy}","received_events_url":"https://api.github.com/users/raphaelhoffmann/received_events","type":"User","site_admin":false},"created_at":"2015-09-07T01:11:00Z","updated_at":"2015-09-07T01:11:00Z","body":"Here are the query plans:\r\n\r\nGreenplum (after ANALYZE):\r\n```\r\n Gather Motion 48:1  (slice2; segments: 48)  (cost=106201580.45..115136401.10 rows=11701014 width=24)\r\n   ->  HashAggregate  (cost=106201580.45..115136401.10 rows=11701014 width=24)\r\n         Group By: t0.id, t1.id, t0.\"rates.id\"\r\n         ->  Hash Join  (cost=13119111.20..98670881.40 rows=11701014 width=24)\r\n               Hash Cond: t0.feature = t1.feature\r\n               ->  Seq Scan on dd_query_rates_features t0  (cost=0.00..7913286.40 rows=11701014 width=39)\r\n               ->  Hash  (cost=5525981.00..5525981.00 rows=10945052 width=37)\r\n                     ->  Broadcast Motion 48:48  (slice1; segments: 48)  (cost=0.00..5525981.00 rows=10945052 width=37)\r\n                           ->  Seq Scan on dd_weights_rates_features t1  (cost=0.00..162905.52 rows=228022 width=37)\r\n```\r\n\r\nPGXL (after redistribution and ANALYZE):\r\n```\r\n Unique  (cost=300927152.97..315138286.41 rows=1421113344 width=24)\r\n   ->  Remote Subquery Scan on all (data1,data10,data11,data12,data13,data14,data15,data16,data17,data18,data19,data2,data20,data21,data22,data23,data24,data25,data26,data27,data28,data29,data3,data30,data31,data32,data4,data5,data6,data7,data8,data9)  (cost=300927152.97..304479936.33 rows=1421113344 width=24)\r\n         ->  Unique  (cost=300927152.97..304479936.33 rows=1421113344 width=24)\r\n               ->  Sort  (cost=300927152.97..304479936.33 rows=1421113344 width=24)\r\n                     Sort Key: t0.id, t1.id, t0.\"ismassageparlorad.id\"\r\n                     ->  Hash Join  (cost=9161.86..55742915.54 rows=1421113344 width=24)\r\n                           Hash Cond: (t0.\"ismassageparlorad.feature\" = t1.\"ismassageparlorad.feature\")\r\n                           ->  Seq Scan on dd_query_f_ismassageparlorad_features_x t0  (cost=0.00..23758703.44 rows=1421113344 width=22)\r\n                           ->  Hash  (cost=6040.27..6040.27 rows=249727 width=70)\r\n                                 ->  Seq Scan on dd_weights_f_ismassageparlorad_features t1  (cost=0.00..6040.27 rows=249727 width=70)\r\n```\r\n\r\n(The unique is unnecessary and it is dropped if you remove DISTINCT from the query; when executing, I ran without DISTINCT.)"}}