Re: Performance Problem

On Mon, May 12, 2003 at 09:57:12 -0400,
  Adam Sherman <adam@tritus.ca> wrote:
>
> BEGIN
> while(list)
> lookup for existing entry (SELECT)
> if not, INSERT
> if yes, UPDATE
> log a message
> COMMIT
>
> With approx. 30K items the process takes way too long. On the order of 10+
> minutes.
>
> I notice that the process seems to slow down as it completes more and more
> items. ie, the first 10K takes just a few moments while the last 10K takes
> most of the time. Note that I'm running test where the only existing
> entries will have been inserted in the same transaction.
>
> I need to know if the bottleneck is PG or higher up so I can go hunting
> there.

Are there deferred constraints? They are still order n squared. Some
fixes went in to 7.3 but the last order n squared issue wasn't fixed
until 7.4. There was some discussion of back porting the fix to 7.3.3
(which might be available in about a month) but I am not sure whether
or that is going to happen.