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;
 
 

Reply via email to