Re: Limit and inherited tables

Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> writes:
> I noticed that LIMIT clause is not pushed down to inherited tables.

It is when appropriate.

> Consider the following tables:

> create table foo(x integer primary key);
> create table foo1 () inherits(foo);
> create table foo2 () inherits(foo);
> insert into foo1 values (generate_series(0,100000));
> insert into foo2 values (generate_series(0,100000));

This example is lacking indexes on the child tables, which is
why the plan shown is about as good as you're going to get.
The contents of foo1 and foo2 have to be read in entirety in any
case, and sorting them separately is not a win compared to doing
a single sort.

With indexes, you get something like

Limit (cost=0.73..0.78 rows=1 width=4)
-> Merge Append (cost=0.73..9778.76 rows=200003 width=4)
Sort Key: foo.x
-> Index Only Scan using foo_pkey on foo (cost=0.12..8.14 rows=1 width=4)
-> Index Only Scan using foo1_x_idx on foo1 (cost=0.29..3050.31 rows=100001 width=4)
-> Index Only Scan using foo2_x_idx on foo2 (cost=0.29..3050.31 rows=100001 width=4)

regards, tom lane