"Guillaume Cottenceau" <gc@xxxxxx> writes: > I think this is the last claimed point which is incorrect. Pg has > no general guarantee the partitions actually create a disjoint > set, even with the CHECK constraints. Pg can only optimize by > avoiding scanning the partitions inside which no satisfactory > data could be found by the CHECK constraint, but I think it's not > possible (too complicated) to infer that any found row in your > other partitions would not be in the final resultset because of > 1. the query's resultset order 2. the limit 3. the actual > conditions in the CHECK constraints (there is no direct way to > see that timestamps in your 200704 partition are greater than > timsteamp in your 200601 partition). I think the answer is that yes there are a number of query transformations that could be done for partitioned tables that we're not doing currently. Generally speaking we need to look at each type of plan node and figure out whether it can usefully be pushed down below the Append node and how we can determine when that can be done. So if each arm of the Append was already in order we could easily push the LIMIT inside the Append (and duplicating the work above the Append). But that wouldn't save us any work because we only generate rows from the partitions as they're needed anyways. In your example we could save work because the Sort needs all the rows before it starts. So we could sort each arm and apply the limit before passing it up to the outer Sort. That might save work in this case. But figuring out when that's less work than just sorting all of them is tricky. If the LIMIT is 1,000 and you have ten arms of 1,000 tuples each then you aren't going to save any work doing this. But if the LIMIT is 1 and you have few arms with many tuples then you could save a lot of work. Actually I happen to have been reading up on algorithms related to this this weekend. It's possible to implement a LimitUnsorted in linear-time which would fetch the first n records according to some sort key without actually sorting the records. That might make it more worthwhile. In short. Yes, there are a lot of optimizations possible around partitioned tables that we don't do either because it's not clear how to tell when they're worthwhile or because the code just isn't there yet. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com
