Re:How good are query optimizers, really? [pdf] (2015)
by ninkendo on Monday July 13, 2020 @08:20PM
(#23827228)
Is the query actually slower, or is it just not using an index you want it to use?
Often times PG won’t bother with an index for a variety of reasons (sequential scans can be legitimately faster in some scenarios), especially when the number of rows is small.
Re:How good are query optimizers, really? [pdf] (2015)
by stubish on Monday July 13, 2020 @09:05PM
(#23827476)
The cool thing about hints is you can quickly drop in a hint to confirm your suspicions and narrow down the problem, rather than trying to do this sort of diagnosis in a vacuum. But because some people use hints for evil, nobody is allowed to use them.
Re:How good are query optimizers, really? [pdf] (2015)
by why-el on Monday July 13, 2020 @10:52PM
(#23827984)
You can also disable seq_scan and force pg to consider indexes, usually that's enough.
Re:How good are query optimizers, really? [pdf] (2015)
by iracic on Tuesday July 14, 2020 @03:11AM
(#23829336)
Access method is just part of the story. Same index may be accessed in different ways, you might also want to combine them. Sometimes you may change table join order to see how it estimates (or executes). Usually there are two parts 1) cost and reasoning for some estimation 2) how it executes (timings, resource usage, locks/contention)
Re:How good are query optimizers, really? [pdf] (2015)
by SigmundA on Tuesday July 14, 2020 @04:12PM
(#23837194)
The query is slower otherwise I would not even notice, I don't care which index it uses if its fast.
It's not just about index usage, its also which type of join (loop, hash or merge) and join order.
Until its not and then I want hints to save my ass, and they are not hints, I want want to TELL the f'ing computer what to do because I know better than the optimizer period.
So surprised to find out PG doesn't support hints don't think I will ever be able to move anything serious until it does, just not going to take that kind of risk.
I have played the whole rewrite query to try and convince the optimizer what to do with barrier tricks, no thanks, give me some hints and I will tell it exactly what to do when thanks.