Re: Partitioning vs. View of a UNION ALL

Ron,

Even though using a view means that it would have to be recreated
> each period as the oldest table is dropped,

please keep in mind:
views are not really "created" ... also the command is named "create view"

VIEWS, at least in PostgreSQL (and Oracle) are nothing else then "macros"
for Queries - the views get substituted within the query with their creation
rule.

That is: the "recreation" of a VIEW is a nearly instant process (it just is
frustrating to connections using this view)

it seems that it would
> still be easier to work with, since you wouldn't have to worry about
> preventing a badly behaving user from inserting into the DDL
> partition's parent table and create 588 CHECK constraints (12 per
> year x 7 years x 7 base tables).

That is true only if you trust your users not to insert into the wrong table
of your 12*7*7 tables.
If you have the appropriate check constraints on your parent table, the
pushing data into the inherited tables should happen automagically (at least
on my databases it does :) )

So... to make sure nobody inserts rubbish you will have to have those 588
check constraints one way or another.
a) to make your partitioning work
b) to ensure nobody inserts data for 2000 into the table for 1900

The most important issue, though, is query speed. Assuming
> excellent index support for query WHERE clauses, regardless of
> whether partitioning or a "viewed UNION ALL", which will the query
> optimizer and constraint_exclusion be more friendly towards?
>

in an optimal world, should'nt those two options be exactly the same?

a) the partition solution:
query planner decides which of your 12*7*7 tables to access and only scans
those. To my undestanding, " constraint_exclusion" only applies to this
solution.

b) the "union all" - or "partitioning by hand":
at the beginning of each partial query there will be an index scan on your
date-column, learning that no data comes from that partial query and planner
skipping on to the next.

Harald

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Python: the only language with more web frameworks than keywords.