--
--  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;