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

2014-10-16 14:04 GMT-03:00 Jeff Janes <jeff(dot)janes(at)gmail(dot)com>:

> 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
>

Like Jeff said, it shouldn't be much of a burden.

If you think it is, than you can query only the last partition (since
partitions are tables themselves).

It seems to me that your application is querying some sample data from the
last date to show something in your application and this approach would do
for that purpose.