Re: Practical question.

louis gonzales escribió:
> louis gonzales wrote:
>
> >2) Seeing as you have no idea - not attacking, stating fact - on the
> >rationale behind the "insert statement-level" to create 1-to-1 table
> >for each statement-level
> >insert, I'd say your presumption is unfounded.

This won't work anyway, because a FOR STATEMENT trigger doesn't have
access to the row being inserted. Even if that worked, consider this
case:

insert into foo values ('one', 'row'), ('two', 'rows');

How do you know you need to create two tables and not just one?

> > If you have some benchmark data, which support why/how to quantify,
> > 50K records in a single table, all of which would have N number of
> > associated records in another table, would out perform 50K records
> > in a single table referencing dedicated 'small' tables, please do
> > share.

Indexes occupy less space in the single table case, and there will be
49999 less catalog entries and 49999 less files on disk. Sure, these
are implementation details; you can ignore them if you want, but the
performance difference is likely to be noticeable.

I'll tell you something: the first question I made to these lists,
around year 2000 IIRC, was around exactly the same issue you are
proposing. A couple of guys suggested the same thing they're telling
you now. I didn't listen and went ahead with this silly idea; and in
time, I understood what they were all about. After much pain, I
redesigned the stuff to use a single table to store all customers' data,
instead of a small table for each customer.

http://archives.postgresql.org/pgsql-general/2000-11/msg00094.php

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support