From: | Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Limit and inherited tables |
Date: | 2016-01-15 14:49:37 |
Message-ID: | 56990701.2060807@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.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
From | Date | Subject | |
---|---|---|---|
Next Message | Merlin Moncure | 2016-01-15 14:50:50 | Re: jsonb - jsonb operators |
Previous Message | Tom Lane | 2016-01-15 14:49:14 | Re: dealing with extension dependencies that aren't quite 'e' |