Partition view optimization

[Stacy White]

I vaguely remember seeing that PostgresSQL optimized selects on certain
UNION ALL statements, so that given:

CREATE VIEW orders AS
SELECT '2003-01' as period, * FROM orders_2003_01
UNION ALL
SELECT '2003-02' as period, * FROM orders_2003_02

then

SELECT * FROM orders WHERE period = '2003-01'

would do a full table scan only on the orders_2003_01 table.

But I didn't see this optimization while I was running some tests against
7.4.2.

Am I missing something now? Or am I just mis-remembering Postgres'
behavior?

[Stacy White]

I see now that the partition view optimization kicks in when I explicitly
cast the period to varchar:

CREATE VIEW orders AS
SELECT '2003-01'::varchar as period, * FROM orders_2003_01
UNION ALL
SELECT '2003-02'::varchar as period, * FROM orders_2003_02

Fair enough, although I'm a little puzzled as to why the plan is different
when the 'period' column is a 'varchar' rather than a 'text'.

"Stacy White" <ha***@computer.org> wrote in message
news:ZuApc.56702$z06.7999140@attbi_s01...

I vaguely remember seeing that PostgresSQL optimized selects on certain
UNION ALL statements, so that given:

CREATE VIEW orders AS
SELECT '2003-01' as period, * FROM orders_2003_01
UNION ALL
SELECT '2003-02' as period, * FROM orders_2003_02

then

SELECT * FROM orders WHERE period = '2003-01'

would do a full table scan only on the orders_2003_01 table.

But I didn't see this optimization while I was running some tests against
7.4.2.

Am I missing something now? Or am I just mis-remembering Postgres'
behavior?