ORDER BY...LIMIT optimization does not work with inherited tables - Mailing list pgsql-bugs

PostgreSQL 8.3 added a new optimization to avoid sorting in queries that
use ORDER BY ... LIMIT.  This optimization does not work when the query
is issued to a parent table with several children, such as a partitioned
table.

PostgreSQL version: 8.3.1
Operating System:    RedHat Enterprise Linux 4

The attached example shows a query to a single table that uses the ORDER
BY ... LIMIT optimization.  Then it shows that the query does not
optimize (uses sort) when executed to the parent of that table.  Execute
as a user that can create databases as "psql -f
order_by_limit_partition_test.sql postgres".  It creates a database
called test_order_by_limit_db.






--
--  Create and connect to the test database
--
DROP DATABASE IF EXISTS test_order_by_limit_db;
CREATE DATABASE test_order_by_limit_db;

\connect test_order_by_limit_db;

--
--  Make a parent table and three child tables partitioned by time using created_at column.
--
CREATE TABLE test_bulletins (
    created_at timestamp with time zone PRIMARY KEY,
    data text NOT NULL DEFAULT 'TEST MESSAGE'
);

CREATE TABLE test_bulletins_20060908 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-08
00:00:00+00'::timestampwith time zone) AND (created_at < '2006-09-09 00:00:00+00'::timestamp with time zone))) 
)
INHERITS (test_bulletins);
ALTER TABLE test_bulletins_20060908 ADD CONSTRAINT test_bulletins_20060908_pkey PRIMARY KEY(created_at);

CREATE TABLE test_bulletins_20060909 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-09
00:00:00+00'::timestampwith time zone) AND (created_at < '2006-09-10 00:00:00+00'::timestamp with time zone))) 
)
INHERITS (test_bulletins);
ALTER TABLE test_bulletins_20060909 ADD CONSTRAINT test_bulletins_20060909_pkey PRIMARY KEY(created_at);

CREATE TABLE test_bulletins_20060910 (CONSTRAINT time_partition_limits CHECK (((created_at >= '2006-09-10
00:00:00+00'::timestampwith time zone) AND (created_at < '2006-09-11 00:00:00+00'::timestamp with time zone))) 
)
INHERITS (test_bulletins);
ALTER TABLE test_bulletins_20060910 ADD CONSTRAINT test_bulletins_20060910_pkey PRIMARY KEY(created_at);

--
--  Populate tables with one values per second
--
INSERT INTO test_bulletins_20060908 (SELECT '2006-09-08 00:00:00+00'::timestamptz + (s.a || ' seconds')::interval as
created_at,'TEST MESSAGE' as data from generate_series(0,86399) as s(a)); 
VACUUM ANALYZE test_bulletins_20060908;

INSERT INTO test_bulletins_20060909 (SELECT '2006-09-09 00:00:00+00'::timestamptz + (s.a || ' seconds')::interval as
created_at,'TEST MESSAGE' as data from generate_series(0,86399) as s(a)); 
VACUUM ANALYZE test_bulletins_20060909;

INSERT INTO test_bulletins_20060910 (SELECT '2006-09-10 00:00:00+00'::timestamptz + (s.a || ' seconds')::interval as
created_at,'TEST MESSAGE' as data from generate_series(0,86399) as s(a)); 
VACUUM ANALYZE test_bulletins_20060910;
--
--  Setup environment for queries.
--
SET constraint_exclusion = on;
\pset footer off;

--
--  Do test case queries.
--
SELECT 'Query explicitly to a single partition (does not use Sort)' as "Test case 1";
EXPLAIN ANALYZE SELECT * FROM test_bulletins_20060909 WHERE created_at > '2006-09-09 00:00:00+00'::timestamptz ORDER BY
created_atASC limit 10; 

SELECT 'Same query, but through parent table (Uses Sort)' as "Test case 2";
EXPLAIN ANALYZE SELECT * FROM test_bulletins WHERE created_at > '2006-09-09 00:00:00+00'::timestamptz ORDER BY
created_atASC limit 10;