Cardinality feedback

[legrandlegrand]

Hello,

Thanks for this very usefull feature in Postgresql !
I'm an old Oracle DBA, and I like it very much ;o)

Question regarding wrong estimates and Oracle Cardinality Feedback
as discussed in https://www.postgresql.org/message-id/flat/1510775864811-0.post%40n3.nabble.com#1510775864811-0.post@n3.nabble.com

do you think that it would be possible to write an extension like auto_explain (or other)
that would be able to catch wrong estimates / real data fetched at the end (success, error, cancel) of each query and add a row in hint_plan.hints table with the proper Hint for row number correction ?

This hint in hint_plan.hints would be available for next executions.
Regards
PAscal

[yamatattsu]

Hi, legrandlegrand, I'm Tatsuro Yamada and I'm a colleague of Kyotaro Horiguchi. I'm working on creating same feature (tool) as you said like a cardinarity feedback. In below my talk, I share my thought such as using feedback loop to get new efficent plan. It means sql tuning advisor by using cardinarity feedback on Oracle. - PGCon 2016 ( you can read it from Page 47) https://www.pgcon.org/2016/schedule/attachments/422_A%20Challenge%20of%20Huge%20Billing%20System%20Migration_20160520.pdf pg_store_plan is able to use analytic of plans (estimate rows vs actual rows and so on), pg_hin_plan is only used for modify cost on paths. So I use both tools for creating my tool. Do you have interested in it? Regards, Tatsuro Yamada 2017-11-19 6:05 GMT+09:00 legrandlegrand <notifications@github.com>:

…

Hello, Thanks for this very usefull feature in Postgresql ! I'm an old Oracle DBA, and I like it very much ;o) Question regarding wrong estimates and Oracle Cardinality Feedback as discussed in https://www.postgresql.org/message-id/flat/1510775864811- ***@***.*** do you think that it would be possible to write an extension like auto_explain (or other) that would be able to catch wrong estimates / real data fetched at the end (success, error, cancel) of each query and add a row in hint_plan.hints table with the proper Hint for row number correction ? This hint in hint_plan.hints would be available for next executions. Regards PAscal — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#5>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AOR4G2_FnHodxjgEnudOg7VXjdZoZXnxks5s30abgaJpZM4QjJkk> .

[legrandlegrand]

Thanks a lot for all thoses informations.

rem: It seems that multivariate statistitics doesn't fix (yet) the cross-table selectivity problem.

Yes I'm interested in your tool, mainly for:

• understanding how to query pg_store_plan to get estimates rows vs actual rows,
• identifiy rules for creating hints regarding those estimates mistakes,
• ...

Regards
PAscal

[legrandlegrand]

it seems that
https://github.com/postgrespro/aqo
proposes something similar

Regards
PAscal

[yamatattsu]

2018/07/11 5:09 "legrandlegrand" <notifications@github.com>:

it seems that https://github.com/postgrespro/aqo proposes something similar

Thanks for your information. Yeah, I know that is similar product and it is sophisticated than my tool which I created. However I believe that my tool has a good point, that is based on using optimizer hints. For example, users are able to know how it works by reading optimizer hints (this is like a sql profile in oracle). And also users can modify the hints as users want. For now, my tool's status is POC. I'm going to share my tool and benchmark results in conference. I'm happy if I can discuss it with you in conference because you know oracle and postgresql like me. Thanks, Tatsuro Yamada

[legrandlegrand]

It seems you tool is alive now
https://github.com/ossc-db/pg_plan_advsr/commits/master

closing this issue