/* * ------------------------------------------- * NOTE: This test behaves differenly on 9.5 * ------------------------------------------- */ \set VERBOSITY terse SET search_path = 'public'; CREATE SCHEMA pathman; CREATE EXTENSION pg_pathman SCHEMA pathman; CREATE SCHEMA test; SET enable_indexscan = ON; SET enable_seqscan = OFF; /* Temporary table for JOINs */ CREATE TABLE test.tmp (id INTEGER NOT NULL, value INTEGER NOT NULL); INSERT INTO test.tmp VALUES (1, 1), (2, 2); /* Partition table by RANGE */ CREATE TABLE test.range_rel ( id SERIAL PRIMARY KEY, dt TIMESTAMP NOT NULL, value INTEGER); INSERT INTO test.range_rel (dt, value) SELECT g, extract(day from g) FROM generate_series('2010-01-01'::date, '2010-12-31'::date, '1 day') AS g; SELECT pathman.create_range_partitions('test.range_rel', 'dt', '2010-01-01'::date, '1 month'::interval, 12); create_range_partitions ------------------------- 12 (1 row) /* * Test UPDATE and DELETE */ /* have partitions for this 'dt' */ EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15'; QUERY PLAN -------------------------------------------------------------------------------- Update on range_rel_6 -> Seq Scan on range_rel_6 Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone) (3 rows) BEGIN; UPDATE test.range_rel SET value = 111 WHERE dt = '2010-06-15'; SELECT * FROM test.range_rel WHERE dt = '2010-06-15'; id | dt | value -----+--------------------------+------- 166 | Tue Jun 15 00:00:00 2010 | 111 (1 row) ROLLBACK; /* have partitions for this 'dt' */ EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt = '2010-06-15'; QUERY PLAN -------------------------------------------------------------------------------- Delete on range_rel_6 -> Seq Scan on range_rel_6 Filter: (dt = 'Tue Jun 15 00:00:00 2010'::timestamp without time zone) (3 rows) BEGIN; DELETE FROM test.range_rel WHERE dt = '2010-06-15'; SELECT * FROM test.range_rel WHERE dt = '2010-06-15'; id | dt | value ----+----+------- (0 rows) ROLLBACK; /* no partitions for this 'dt' */ EXPLAIN (COSTS OFF) UPDATE test.range_rel SET value = 222 WHERE dt = '1990-01-01'; QUERY PLAN -------------------------------------------------------------------------------- Update on range_rel -> Seq Scan on range_rel Filter: (dt = 'Mon Jan 01 00:00:00 1990'::timestamp without time zone) (3 rows) BEGIN; UPDATE test.range_rel SET value = 111 WHERE dt = '1990-01-01'; SELECT * FROM test.range_rel WHERE dt = '1990-01-01'; id | dt | value ----+----+------- (0 rows) ROLLBACK; /* no partitions for this 'dt' */ EXPLAIN (COSTS OFF) DELETE FROM test.range_rel WHERE dt < '1990-01-01'; QUERY PLAN -------------------------------------------------------------------------------- Delete on range_rel -> Seq Scan on range_rel Filter: (dt < 'Mon Jan 01 00:00:00 1990'::timestamp without time zone) (3 rows) BEGIN; DELETE FROM test.range_rel WHERE dt < '1990-01-01'; SELECT * FROM test.range_rel WHERE dt < '1990-01-01'; id | dt | value ----+----+------- (0 rows) ROLLBACK; /* UPDATE + FROM, partitioned table */ EXPLAIN (COSTS OFF) UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id; QUERY PLAN -------------------------------------------------------------------------------------------- Update on range_rel_1 r -> Hash Join Hash Cond: (t.id = r.id) -> Seq Scan on tmp t -> Hash -> Index Scan using range_rel_1_pkey on range_rel_1 r Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone) (7 rows) BEGIN; UPDATE test.range_rel r SET value = t.value FROM test.tmp t WHERE r.dt = '2010-01-01' AND r.id = t.id; ROLLBACK; /* UPDATE + FROM, single table */ EXPLAIN (COSTS OFF) UPDATE test.tmp t SET value = r.value FROM test.range_rel r WHERE r.dt = '2010-01-01' AND r.id = t.id; QUERY PLAN -------------------------------------------------------------------------------------------------- Update on tmp t -> Hash Join Hash Cond: (t.id = r.id) -> Seq Scan on tmp t -> Hash -> Append -> Index Scan using range_rel_1_pkey on range_rel_1 r Filter: (dt = 'Fri Jan 01 00:00:00 2010'::timestamp without time zone) (8 rows) BEGIN; UPDATE test.tmp t SET value = r.value FROM test.range_rel r WHERE r.dt = '2010-01-01' AND r.id = t.id; ROLLBACK; /* DELETE + USING, partitioned table */ EXPLAIN (COSTS OFF) DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id; QUERY PLAN -------------------------------------------------------------------------------------------- Delete on range_rel_1 r -> Hash Join Hash Cond: (t.id = r.id) -> Seq Scan on tmp t -> Hash -> Index Scan using range_rel_1_pkey on range_rel_1 r Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone) (7 rows) BEGIN; DELETE FROM test.range_rel r USING test.tmp t WHERE r.dt = '2010-01-02' AND r.id = t.id; ROLLBACK; /* DELETE + USING, single table */ EXPLAIN (COSTS OFF) DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id; QUERY PLAN -------------------------------------------------------------------------------------------------- Delete on tmp t -> Hash Join Hash Cond: (t.id = r.id) -> Seq Scan on tmp t -> Hash -> Append -> Index Scan using range_rel_1_pkey on range_rel_1 r Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone) (8 rows) BEGIN; DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id; ROLLBACK; /* Test special rule for CTE; SELECT (PostgreSQL 9.5) */ EXPLAIN (COSTS OFF) WITH q AS (SELECT * FROM test.range_rel r WHERE r.dt = '2010-01-02') DELETE FROM test.tmp USING q; QUERY PLAN ---------------------------------------------------------------------------------------- Delete on tmp CTE q -> Append -> Seq Scan on range_rel_1 r Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone) -> Nested Loop -> CTE Scan on q -> Materialize -> Seq Scan on tmp (9 rows) BEGIN; WITH q AS (SELECT * FROM test.range_rel r WHERE r.dt = '2010-01-02') DELETE FROM test.tmp USING q; ROLLBACK; /* Test special rule for CTE; DELETE (PostgreSQL 9.5) */ EXPLAIN (COSTS OFF) WITH q AS (DELETE FROM test.range_rel r WHERE r.dt = '2010-01-02' RETURNING *) DELETE FROM test.tmp USING q; QUERY PLAN ---------------------------------------------------------------------------------------- Delete on tmp CTE q -> Delete on range_rel_1 r -> Seq Scan on range_rel_1 r Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone) -> Nested Loop -> CTE Scan on q -> Materialize -> Seq Scan on tmp (9 rows) BEGIN; WITH q AS (DELETE FROM test.range_rel r WHERE r.dt = '2010-01-02' RETURNING *) DELETE FROM test.tmp USING q; ROLLBACK; /* Test special rule for CTE; DELETE + USING (PostgreSQL 9.5) */ EXPLAIN (COSTS OFF) WITH q AS (DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id RETURNING *) DELETE FROM test.tmp USING q; QUERY PLAN ---------------------------------------------------------------------------------------------------------- Delete on tmp CTE q -> Delete on tmp t -> Hash Join Hash Cond: (t.id = r.id) -> Seq Scan on tmp t -> Hash -> Append -> Index Scan using range_rel_1_pkey on range_rel_1 r Filter: (dt = 'Sat Jan 02 00:00:00 2010'::timestamp without time zone) -> Nested Loop -> CTE Scan on q -> Materialize -> Seq Scan on tmp (14 rows) BEGIN; WITH q AS (DELETE FROM test.tmp t USING test.range_rel r WHERE r.dt = '2010-01-02' AND r.id = t.id RETURNING *) DELETE FROM test.tmp USING q; ROLLBACK; DROP SCHEMA test CASCADE; NOTICE: drop cascades to 15 other objects DROP EXTENSION pg_pathman CASCADE; DROP SCHEMA pathman CASCADE;