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;