Hi Jeff, Thanks for clarifications!
In my case yes, it's just few blocks, but different ones every time I change user_id value in my WHERE clause. When I change user_id - buffers are no longer "shared hit" in EXPLAIN. This is a bit more worrying. But if there is no easy fix - well, OK. Best regards, Dmitriy Shalashov 2014-10-16 21:04 GMT+04:00 Jeff Janes <jeff.ja...@gmail.com>: > On Thu, Oct 16, 2014 at 5:35 AM, Дмитрий Шалашов <skau...@gmail.com> > wrote: > >> Hi, >> >> lets imagine that we have some table, partitioned by timestamp field, and >> we query it with SELECT with ordering by that field (DESC for example), >> with some modest limit. >> Lets further say that required amount of rows is found in the first table >> that query encounters (say, latest one). >> I am just wondering, why nevertheless PostgreSQL does read couple of >> buffers from each of the older tables? >> > > The planner only does partition pruning statically, not dynamically.The > LIMIT has to be implemented dynamically--it cannot prove absolutely that > the "first" partition will have enough rows, so it cannot eliminate the > others. > > The "Merge Append" does a priority queue merge, and so needs to read the > "first" row (according to the ORDER BY) from each partition in order to seed > the priority queue. I guess what it could be made to do in the case where > there are suitable check constraints on a partition, is seed the priority > queue with a dummy value constructed from the constraint. If the merge > never gets far enough to draw upon that dummy value, then that whole plan > node never needs to get started up. > > In your case that would save very little, as reading a few blocks for each > partition is not much of a burden. Especially as it the same few blocks > every time, so they should be well cached. There may be other case where > this would be more helpful. But it isn't clear to me how the planner could > build such a feature into its cost estimates, and the whole thing would be > a rather complex and esoteric optimization to make for uncertain gain. > > Cheers, > > Jeff >
