Re: ORDER BY...LIMIT optimization does not work with inherited tables

I have just run into this issue myself and I was wondering if it is
considered a bug or a missing feature? Is there a plan to address this
in an upcoming release? Has anyone found a good work around to the
problem in the interim?

Thanks!
John.

On Wed, May 7, 2008 at 4:38 AM, Marshall, Steve <smarshall@wsi.com> wrote:
> 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'::timestamp with 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'::timestamp with 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'::timestamp with 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_at ASC 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_at ASC limit 10;
>
>
>
> --
> Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
>