{"action":"created","issue":{"url":"https://api.github.com/repos/tigvarts/aqo/issues/2","repository_url":"https://api.github.com/repos/tigvarts/aqo","labels_url":"https://api.github.com/repos/tigvarts/aqo/issues/2/labels{/name}","comments_url":"https://api.github.com/repos/tigvarts/aqo/issues/2/comments","events_url":"https://api.github.com/repos/tigvarts/aqo/issues/2/events","html_url":"https://github.com/tigvarts/aqo/issues/2","id":327097760,"number":2,"title":"Cardinality estimates on window aggs not changing","user":{"login":"JimFAmazon","id":39709483,"avatar_url":"https://avatars1.githubusercontent.com/u/39709483?v=4","gravatar_id":"","url":"https://api.github.com/users/JimFAmazon","html_url":"https://github.com/JimFAmazon","followers_url":"https://api.github.com/users/JimFAmazon/followers","following_url":"https://api.github.com/users/JimFAmazon/following{/other_user}","gists_url":"https://api.github.com/users/JimFAmazon/gists{/gist_id}","starred_url":"https://api.github.com/users/JimFAmazon/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/JimFAmazon/subscriptions","organizations_url":"https://api.github.com/users/JimFAmazon/orgs","repos_url":"https://api.github.com/users/JimFAmazon/repos","events_url":"https://api.github.com/users/JimFAmazon/events{/privacy}","received_events_url":"https://api.github.com/users/JimFAmazon/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2018-05-28T18:54:53Z","updated_at":"2018-05-29T22:37:31Z","closed_at":null,"author_association":"NONE","body":"The following plan shows a large cardinality estimation error.  AQO isn't able to improve the estimate.  Is this hitting a known limitation of AQO?  \r\n\r\nThere is also an index-based plan for delivering the order needed by the aggregate has lower startup cost, and should have been selected in this case because of the LIMIT 1.\r\n\r\nThe plan:\r\n\r\n Limit  (cost=6479.18..6498.46 rows=1 width=4) (actual time=51.979..51.979 rows=1 loops=1)\r\n   ->  Subquery Scan on tmp  (cost=6479.18..6498.46 rows=1 width=4) (actual time=51.978..51.978 rows=1 loops=1)\r\n         Filter: ((tmp.pstn_vrsn_nbr = tmp.a) AND (((tmp.prty_1_trd_rfrnc_id = 'MQa4147f9c7ac809116532482232ee0630'::text) AND (tmp.prty_1_id = 'EQP2317'::text)) OR ((tmp.prty_2_trd_rfrnc_id = 'MQ7867600a47151071bd01bc6c71c0f4d9'::text) AND (tmp.prty_2_id = 'EQP2895'::text))))\r\n         ->  WindowAgg  (cost=6479.18..6487.62 rows=482 width=96) (actual time=51.974..51.974 rows=1 loops=1)\r\n               **->  Sort  (cost=6479.18..6480.39 **rows=482** width=92) (actual time=51.962..51.962 **rows=2** loops=1)**\r\n                     Sort Key: pos.dtcc_trd_ref_id\r\n                     Sort Method: quicksort  Memory: 102kB\r\n                     ->  Gather  (cost=1000.00..6457.70 rows=482 width=92) (actual time=0.343..51.517 rows=519 loops=1)\r\n                           Workers Planned: 2\r\n                           Workers Launched: 2\r\n                           ->  Parallel Seq Scan on \"position\" pos  (cost=0.00..5409.50 rows=201 width=92) (actual time=0.314..41.780 rows=173 loops=3)\r\n                                 Filter: ((prty_1_id = 'EQP2317'::text) OR (prty_2_id = 'EQP2895'::text))\r\n                                 Rows Removed by Filter: 83160\r\n Planning time: 0.747 ms\r\n Execution time: 53.444 ms\r\n Using aqo: true\r\n(16 rows)\r\n\r\nThe SQL:\r\n\r\nexplain analyze                                                                                                                                                                                         \r\nSELECT 1                                                                                                                                                                                                \r\nFROM                                                                                                                                                                                                    \r\n    (SELECT                                                                                                                                                                                             \r\n        dtcc_trd_ref_id,                                                                                                                                                                                \r\n        pstn_vrsn_nbr,                                                                                                                                                                                  \r\n        prty_1_trd_rfrnc_id,                                                                                                                                                                            \r\n        prty_1_id,                                                                                                                                                                                      \r\n        prty_2_trd_rfrnc_id,                                                                                                                                                                            \r\n        prty_2_id ,                                                                                                                                                                                     \r\n        MAX(pstn_vrsn_nbr) OVER (PARTITION BY dtcc_trd_ref_id) AS a                                                                                                                                     \r\n    FROM ddl_position.position pos                                                                                                                                                                      \r\n    WHERE (prty_1_id = 'EQP2317' or prty_2_id = 'EQP2895')                                                                                                                                         \r\n    ) as tmp                                                                                                                                                                                            \r\nWHERE                                                                                                                                                                                                   \r\npstn_vrsn_nbr = a                                                                                                                                                                                                                                                                                             \r\nAND ((prty_1_trd_rfrnc_id = 'MQa4147f9c7ac809116532482232ee0630' AND prty_1_id = 'EQP2317') OR                                                                                                          \r\n     (prty_2_trd_rfrnc_id = 'MQ7867600a47151071bd01bc6c71c0f4d9' AND prty_2_id = 'EQP2895')                                                                                                             \r\n    )                                                                                                                                                                                                   \r\nlimit 1 ; "},"comment":{"url":"https://api.github.com/repos/tigvarts/aqo/issues/comments/392970298","html_url":"https://github.com/tigvarts/aqo/issues/2#issuecomment-392970298","issue_url":"https://api.github.com/repos/tigvarts/aqo/issues/2","id":392970298,"user":{"login":"tigvarts","id":8570688,"avatar_url":"https://avatars1.githubusercontent.com/u/8570688?v=4","gravatar_id":"","url":"https://api.github.com/users/tigvarts","html_url":"https://github.com/tigvarts","followers_url":"https://api.github.com/users/tigvarts/followers","following_url":"https://api.github.com/users/tigvarts/following{/other_user}","gists_url":"https://api.github.com/users/tigvarts/gists{/gist_id}","starred_url":"https://api.github.com/users/tigvarts/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tigvarts/subscriptions","organizations_url":"https://api.github.com/users/tigvarts/orgs","repos_url":"https://api.github.com/users/tigvarts/repos","events_url":"https://api.github.com/users/tigvarts/events{/privacy}","received_events_url":"https://api.github.com/users/tigvarts/received_events","type":"User","site_admin":false},"created_at":"2018-05-29T22:37:31Z","updated_at":"2018-05-29T22:37:31Z","author_association":"OWNER","body":"This plan looks like it should have been improved by AQO. Are you sure that the method converged (i. e. plan stopped changing)? Often one needs to run a query several times with learn_aqo=True and use_aqo=True before AQO fixes all cardinality estimation errors and the plan stabilizes."}}
{"actor":{"display_login":"tigvarts"}}