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

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

From
"Marshall, Steve"
Date:
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; 


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

From
"John Smith"
Date:
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
>
>

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

From
Heikki Linnakangas
Date:
John Smith wrote:
> I have just run into this issue myself and I was wondering if it is
> considered a bug or a missing feature?

A missing feature.

> Is there a plan to address this
> in an upcoming release?

Greg Stark worked on so-called "Ordered Append" patch in spring, which
would have solved this, but he didn't get around to finish it for 8.4.
Maybe 8.5...

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com