470,614 Members | 1,425 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,614 developers. It's quick & easy.

Partition view optimization

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?
Jul 19 '05 #1
1 3937
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?

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by jota | last post: by
4 posts views Thread by Neil | last post: by
1 post views Thread by Mark Dengler | last post: by
5 posts views Thread by sameer_deshpande | last post: by
debasisdas
reply views Thread by debasisdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.