Hi,
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.03rows=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