Re: Partitioned tables and SELECT ... ORDER BY ... LIMIT

On Thu, Oct 16, 2014 at 5:35 AM, Дмитрий Шалашов <skaurus(at)gmail(dot)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