We have a large DB
with partitioned tables in postgres. We have had trouble with a
ORDER/LIMIT type query. The order and limit are not pushed down to the
sub-tables....
CREATE TABLE base
(
foo int
);
CREATE TABLE
bar_0
extra int
) INHERITS
(base);
ALTER TABLE bar ADD
PRIMARY KEY (foo);
-- repeated for
bar_0... bar_40
SELECT foo FROM base
ORDER BY foo LIMIT 10;
is real slow. What
is required to make the query planner generate the following instead... (code
change i know, but how hard would it be?)
SELECT
foo
FROM
(
SELECT
*
FROM bar_0
ORDER BY foo LIMIT
10
UNION ALL
SELECT
*
FROM bar_1
ORDER BY foo LIMIT
10
....
) AS base
ORDER BY foo
LIMIT 10;
