Re: views, queries, and locks

On Wed, Apr 4, 2012 at 10:43 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> writes:
>> On Wed, Apr 4, 2012 at 9:01 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Why aren't you using a standard partitioned table, cf
>>> http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html
>
>> Because I'm adding "scalar" (constant-value) columns to the view like this:
>> SELECT * from tableA, DATE 'date string here' as date_column
>> UNION ALL
>> SELECT * from tableB, DATE 'date string here' as date_column
>
>> for hundreds or even thousands of tables.
>
> [ yawn... ]  Premature micro-optimization is the root of all evil.
> The actual advantage to what you are doing is not scanning irrelevant
> partitions, which constraint exclusion handles perfectly fine.  Not
> storing the date column is unlikely to be saving anything meaningful.
> (How wide are those table rows, anyway?)

I agree, generally, however as with a great many things in life, what
it does now what it was designed to do are two different things. Quite
frankly, it's a testament to PostgreSQL that it handles this situation
(which is many times greater than the original design) as well as it
does.

Regarding the storage costs for adding a column:
A quick back-of-the-napkin means the table size increase is roughly 5%.
I'll have to determine if the size tradeoff (+ table inheritance) is
worth it versus using the view.

Thanks for the advice, everyone.

--
Jon