Limit and inherited tables
От | Konstantin Knizhnik |
---|---|
Тема | Limit and inherited tables |
Дата | |
Msg-id | 56990701.2060807@postgrespro.ru обсуждение исходный текст |
Ответы |
Re: Limit and inherited tables
|
Список | pgsql-hackers |
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
В списке pgsql-hackers по дате отправления: