leborchuk opened a new pull request, #900: URL: https://github.com/apache/cloudberry/pull/900
I've made a simple test 1. create demo-cluster 2. execute simple insert values query 1000 times with gporca disabled 3. repeat 2. with gporca enabled 4. compare the results - test with gporca enabled more than 12x slower Here the results: `postgres=# set optimizer=off; SET Time: 1.657 ms postgres=# do $$ begin for i in 1..1000 loop insert into test values(i); end loop; end; $$; DO Time: 801.485 ms postgres=# set optimizer=on; SET Time: 1.540 ms postgres=# do $$ begin for i in 1..1000 loop insert into test values(i); end loop; end; $$; DO Time: 10751.109 ms (00:10.751)` Honestly, the expected result. Integration with gporca includes a large number of copies and transformations. In this PR, I propose disabling gporca for simple queries such as insert values. Of course, users could do the same manually, but I have not heard anyone actually doing so. Therefore, it would be great if the database switches to the postgres optimizer if a query is too simple to use gporca. We know that gporca certainly won't produce a better execution plan. I formalized it in the enabled_for_optimizer function. We use postgres optimizer if we do not use any of: aggregation, with clause, recurse clause, window functions. And the number of relations in a query less or equal optimizer_relations_threshold. Otherwise, use gporca. P.S. This was inspired by conclusions from the [Integrating the Orca Optimizer into MySQL](https://openproceedings.org/2022/conf/edbt/paper-87.pdf) article. One conclusion was that it is not advisable to use gporca for simple queries. Let's implement this ) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
