2

I'm studying query optimization and want to know how much each kind of optimizations help the query. Last time, I got an answer but when in my experiments, disable all optimization in the link has time complicity of O(n^1.8) enable all of them has O(n^0.5). there is not so much difference, if disable all of them, is there still other optimizations? how can I really have only one main optimizations each time?

Community
  • 1
  • 1
user3419945
  • 347
  • 1
  • 2
  • 17
  • Implicit in your question is the notion that there is some "default" way to execute every query, and you can get back to that default way just by not doing something. I don't think that that is the way the planner works. Also, the difference between n^0.5 and n^1.8 can be huge. – jjanes Apr 04 '14 at 15:21

1 Answers1

2

You can't.

PostgreSQL's query planner has no "turn off optimisation" flag.

It'd be interesting to add, but would make the regression tests a lot more complex, and be of very limited utility.

To do what you want, I think you'd want to modify the query planner code, recompile, and reinstall PostgreSQL for each test. Or hack it to add a bunch of custom GUCs (system variables, like enable_seqscan) to let you turn particular optimisations on and off.

I doubt any such patch would be accepted into PostgreSQL, but it'd be worth doing as a throwaway.

The only challenge is that PostgreSQL doesn't differentiate strongly between "optimisation" and "thing we do to execute the query". Sometimes parts of the planner code expect and require that a particular optimisation has been applied in order to work correctly.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Thanks, I guess I have to modify the source code of optimizer. However it seems rather complicated. Could you please suggest some documents or book about the detail architecture of postgresql optimizer? I did not find one ... – user3419945 Apr 07 '14 at 13:25
  • Take a look at the developers section of the postgresql site and the developers postgresql wiki entry. There are some resources there. The Pg documentation has some internals information too. The best resource is the source code its self - comments. Yes, it is complicated. – Craig Ringer Apr 07 '14 at 23:10