Re: huge performance penalty from constraint triggers

On Thu, 1 Aug 2002, Ben Liblit wrote:

> I have a modest-sized PostgreSQL database, with about four thousand
> records across three tables. I am seeing an astonishing variance in the
> time it takes to initially populate the database versus the time it
> takes to restore it from a standard pg_dump archive. Initial population
> takes nineteen hours, while restoring the dump takes a mere twenty one
> seconds!

Well, I see one big problem with your schema. Sites has site as a bigint
while samples has it as an int. On my machine the difference between
having them match and having them different is orders of magnitude because
it can't fully use the index when they're different types and pessimizes
the selects.

To give you an idea for 3100 rows into samples, it took minutes (I stopped
it) with your schema and 3.354 seconds as counted by time when I changed
samples.site to bigint.