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 <[EMAIL PROTECTED]> 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 > > -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
