By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,780 Members | 1,240 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,780 IT Pros & Developers. It's quick & easy.

Partition view optimization

P: n/a
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
Share this Question
Share on Google+
1 Reply

P: n/a
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.