PostgreSQL partition query by date optimization

This function will dynamically query one week at a time up to the limit hopefully taking advantage of the partitioning. SQL Fiddle

create or replace function unit_position_limited_by(l integer)
returns setof unit_position
language plpgsql as $function$

declare
    week timestamp := date_trunc('week', transaction_timestamp());
    total integer := 0;
    inserted integer;
    not_exists boolean;
begin
    loop
        return query execute $$
            select *
            from unit_position
            where
                unit_id = 1
                and obs_time >= $1 and obs_time < $2
            order by obs_time desc
            limit $3
        $$ using week, week + interval '1 week', l - total;
        get diagnostics inserted := row_count;
        total := total + inserted;
        exit when total = l; 
        if inserted = 0 then
            execute $$
                select not exists (
                    select 1
                    from unit_position
                    where obs_time < $1
                    )
            $$ into not_exists using week;
            exit when not_exists;
        end if;
        week := week - interval '1 week';
    end loop;
end; $function$;

To select from it:

select *
from unit_position_limited_by(1000);

PostgreSQL's partitioning is a bit of a hack, and this is one of the areas that shows. There's no "smart partition scan" node type that can lock all partitions but only scan them in order until a rowcount requirement is satisfied.

Partitioning scan limiting is only done with constraint_exclusion, which requires that the query planner be able to prove from constants in the query that partitions will not be required.

A proper solution to your problem would require the addition of a new scan type in PostgreSQL where Pg took a lock on all the partitions when the query began but only scans them until it satisfies a rowcount requirement from an outer plan node.

You've already found one of the better workarounds available to you, adding a constant to limit which partitions are scanned. There is no query hint to limit the partitions scanned, though you can write an explicit plan yourself, like:

  SELECT code, obs_time
    FROM (
        SELECT * FROM unit_position_week_350
        UNION ALL
        SELECT * FROM unit_position_week_349
        UNION ALL
        SELECT * FROM unit_position_week_348
        UNION ALL
        SELECT * FROM unit_position_week_347
        UNION ALL
        SELECT * FROM unit_position_week_346
        UNION ALL
        SELECT * FROM unit_position_week_345
   ) unit_position_350_to_345
   WHERE unit_id = 1
ORDER BY obs_time DESC LIMIT 1000;

... but I haven't tested to see how this plans out and whether it performs decently. You might need to move the ORDER BY into the subquery or even the unit_id if the planner doesn't push that condition down its self.