47 ms·
I find it disapointing that the optimizer of the database is not able to get to the fastest result by itself. A broken promise of declarative languages I guess
by freeman478 13y ago
I find it disapointing that the optimizer of the database is not able to get to the fastest result by itself.
A broken promise of declarative languages I guess.
- joevandyk 13y agoI wish more people were working on planner optimization/stats. It's a hard problem though, I know I'm nowhere near smart enough for it.
- saosebastiao 13y agoI don't think it is a matter of intelligence, just skillset. Query planners could go a long way with a few people with strong relational logic skills that could prove logical equivalence of expressions.
- lvh 13y agoThe declarative language promise was that it would be possible to perform such optimizations. Indeed, by-and-large, query planners do a pretty great job. I don't think it was ever a promise that query planners would be able to always find the fastest query for what you want.
- jerf 13y agoI read the declarative language promise as you will never have to know what's going on under the hood. This is never true in any non-trivial declarative language being used for non-trivial work. I have developed a very negative initial reaction to anything that claims to be "declarative".
- Pxtl 13y agoLikewise. The more declarative a platform is, the more I'm trusting its abstractions to never leak, because when I do I'm going to have to learn every agonizing minute detail of how it works under the hood. SQL is a tiny kernel of beautiful relational mathematics wrapped up in a 3-foot ball of ugly implementation hacks. NoSQL is what happens when you throw out the beautiful kernel and just keep the hacks, so it's not really better I guess.
- danielweber 13y agoYeah, in theory the database is supposed to take care of things like this for you. In practice, every time I've seen a slow DB query, I've always been able to get huge improvements on it, even though the DB in theory had all the knowledge necessary to realize that I don't care about the intermediate results.
- baudehlo 13y agoThere are some settings worth tweaking in postgres' config to improve the query planner. The one that made the biggest difference to us was "cpu_tuple_cost". Setting it to 0.2 (the default is 0.01) made it choose more optimal query plans more often.
- lincolnq 13y agoHuh, interesting! I just looked up the parameter - "the planner's estimate of the cost of processing each row during a query". So increasing this will tend to cause the query planner to forgo complete table scans in favor of indexed queries more often. Increasing the parameter by a factor of 20 seems like you're telling it "never do complete table scans unless it's completely unavoidable, always use an index". I can see why this would make the query plans look better (in that index scans make more sense to humans) but are you sure you're actually getting better performance? If you are getting better performance, why is the default so wrong? Computers are really fast at sequential scans, less so at random-access stuff.
- corresation 13y agoThe default is configured for magnetic disks, where random IO is brutally slow (on the order of 30-100 per second) and sequential reads are reasonably fast. But what if you're running it on a Micron P420m with 700,000 IOPS per second?
- jdub 13y ago700,000 I/Os per second per second? That is some serious storage acceleration you've got there.
- Sami_Lehtinen 13y agoWith proper SSD drives you can get seriously high IOPS. https://www.fusionio.com/products/iodrive2-duo/ https://www.fusionio.com/products/iodrive2-duo/
- 6cxs2hd6 13y agoBut isn't `DISTINCT` sort of shoving an imperative square peg into the round hole of declaritive set relations? At least in my time with MSSQL, seeing `SELECT DISTINCT ...` was a red flag. It nearly always meant something needed to be refactored using GROUP BY.
- pdubs 13y agoSELECT COUNT(DISTINCT ...) is a very different thing. It's counting distinct values of a particular column in a result set and totally not a square peg/round hole situation.
- jeffdavis 13y agoI've never heard DISTINCT described as imperative. It still leaves room to determine how and when to achieve the DISTINCT property, perhaps even doing no work at all. * It can do a sort+unique or a hash, depending on cardinality, availability of memory, and a few other factors. * It can push down a distinct or pull up a distinct, resulting in a faster plan. * If there is another part of the plan that already implies distinct, like a GROUP BY, then it doesn't need to do any work at all. (Or similarly, if you're doing a distinct on a primary key).
- schrodinger 13y agoFYI, in mssql at least, distinct and group by are entirely equivalent to the query planner. I'm not saying you're wrong though, since group by let's you explicitly pick which columns to go by, whereas distinct uses them all.
- alinhan 13y agoI don't understand what you mean by "group by let's you explicitly pick which columns to go by, whereas distinct uses them all". Can you give an example?
- schrodinger 13y agoselect userid, name from users group by userid, name is equivalent to select distinct userid, name from users It's easy to add another column to the "distinct" based query and change the behavior accidentally. With the "group by" query, you'd get an error saying that the new column needs to be either aggregated or added to the group by.
- jeffdavis 13y agoIn postgres, distinct aggregates are a blind spot in the planner. DISTINCT itself is handled well, e.g. "SELECT DISTINCT x, y ...". But the distinct aggregates are not really seen by the planner, and they are always implemented with a sort. So this is a peculiar case for postgres. One reason is because distinct aggregates can be more complicated to plan when you have a case like: "SELECT COUNT(distinct x), COUNT(distinct y) ..." because you actually need two different distinct sets as input. Some databases have solutions for that problem, but postgres does not.
- aaronblohowiak 13y agoWell-documented MIT-licensed code. You can help!
- jeffdavis 13y agoAgreed. There's a commit fest going on right now, anyone should feel free to jump in and review a couple of patches with us. Even if you don't know C. https://commitfest.postgresql.org/action/commitfest_view?id=21 https://commitfest.postgresql.org/action/commitfest_view?id=... Review is a good way to learn about new features, by the way. That's the main reason I'm able to actually write recursive queries, for instance.
- elliptic 13y agoSerious question - how realistic is that? How many people make significant changes to the planner/executor besides Tom Lane?
- jeffdavis 13y agoThis particular change, in it's complete form, would be challenging in my opinion. Many other kinds of planner changes aren't so bad though, but people (including me) tend to avoid them. Executor changes are much less intimidating and lots of people make executor changes. In my opinion, the executor is the most approachable/easiest major component. I would encourage people to not get intimidated though. For every project where I was determined enough, I succeeded. Tom Lane does especially amazing things, of course, but I think reasoning like "I'm not Tom Lane, therefore the planner is too hard" is the wrong lesson to take away.
- corresation 13y agoIt's worth noting that SQL Server, presuming you've configured uniques and relationships correctly, actually runs exactly the same query plan for their first query and their last query. I detailed this at http://dennisforbes.ca/index.php/2014/01/24/your-query-planner-and-you/ http://dennisforbes.ca/index.php/2014/01/24/your-query-plann...