Re: slow join on postgresql6.5

At 12:49 PM 3/30/00 -0500, Bernard Adrian Frankpitt wrote:

>Implementing the change is probably not as bad as it seems, access
>methods are beautifully self-contained in Postgres. The changes are
>quite fundamental though, and you sure would want to get it right. You
>would also want to retain the current behavior for users who do lots of
>updates.

Again, if you read my post carefully I was simply pointing out that
RI semantics will degrade rapidly (for some tables) once the index
is unusable because a full table scan will be necessary (where at
worst Oracle, say, would read the entire index to get the necessary
information, which for some tables won't be nearly as bad as going
to the actual table). I was trying to point out that the degradation
may be more spectacular than, say, with Oracle because PG always
needs to go to the table rather than simply use the data in the
index.

And that it is worth documenting. And that after I do MATCH PARTIAL
semantics for 7.1 I'll try to find time to do some documenting of
RI, including information on "gotchas" such as this one.

(people are likely to be mystified if setting the first column of
a foreign key to NULL makes PG go away for a long time while
setting it to a non-NULL value doesn't, which is exactly what will
happen for SOME large tables if MATCH PARTIAL and MATCH <unspecified>
is used)

- Don Baccus, Portland OR <dhogaza(at)pacifier(dot)com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.