I am sorry if this question was already discussed but I failed to find any information about in archive.
I noticed that LIMIT clause is not pushed down to inherited tables.
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));

explain select * from foo order by x limit 1;
                               QUERY PLAN
 Limit  (cost=5.10..5.10 rows=1 width=4)
   ->  Sort  (cost=5.10..5.61 rows=200000 width=4)
         Sort Key: foo.x
         ->  Append  (cost=0.00..4.06 rows=200000 width=4)
               ->  Seq Scan on foo  (cost=0.00..0.00 rows=1 width=4)
               ->  Seq Scan on foo1  (cost=0.00..2.03 rows=100000 width=4)
               ->  Seq Scan on foo2  (cost=0.00..2.03 rows=100000 width=4)
(7 rows)

So Postgres has to merge two large data sets and sort the result, while the optimal plan is to take just one record from each inherited table, sort 2 records and then limit the result.

Such optimization will be especially useful in case of using postgres_fdw - when inherited tables are located at remote nodes. Are there any plans to support this optimization or may be somebody is already working on it?
Otherwise I can try to investigate it and propose optimizer patch for it.

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:

Reply via email to