Fastest way to fetch one row per partition

user_8381175 0
Viewed 72

I have a table which is partitioned by LIST(id).

create table a (
  id serial not null,
  col1 int not null,
  col2 int null,
  col3 <type3>, 
  ...,
  coln <typen>

) partition by list(id);

In a given partition, col2 can either all be null or all be not null. What I wish for is the fastest way to fetch one row with id, col1, col2 per partition. Right now, the fastest way I have of doing this is:

select id, col1, col2 from (select row_number() over (partition a by id) as r,t.id, t.col1, t.col2 from a t) x where x.r =1;

However, this still has to do an append +seq scan on each individual partition followed by a sort on id, before it runs a windowAgg and actually runs the filter.

Is there a way to ensure that it only appends one row per partition on the append step itself? Failing that, how would I optimize this query such that the number of rows in each sub-partition don't matter and the query is agnostic of this?

postgresql sql
1 Answers

There is no way to do that with a single query.

But you can construct a metadata query that will result in a fast query.

I use psql's exec to execute this dynamic query, but you can do it as you please:

SELECT string_agg(
          format(
             '(SELECT id, col1, col2 FROM %s LIMIT 1)',
             inhrelid::regclass
          ),
          E'\nUNION ALL\n'
       )
FROM pg_inherits
WHERE inhparent = 'a'::regclass \gexec
user_64643080
Related