Thread: followup to SELECT/INSERT problem

ok, so i decided to run a test with just the one element of my pipeline.


the process does:

BEGIN WORK;
loop
    read stdin
    if changes
          UPDATE table1 SET ... WHERE KEY = ...;
    print to stdout
END WORK;

now i fire up:
$ cat file.dat | adjust-rec > /dev/null

it works fine until the first update, then the backend goes into UPDATE and
starts eating CPU.

it appears that each call to UPDATE seems to be taking a long, long time to
complete.

--
[ Jim Mercer                 jim@reptiles.org              +1 416 410-5633 ]
[          Reptilian Research -- Longer Life through Colder Blood          ]
[  Don't be fooled by cheap Finnish imitations; BSD is the One True Code.  ]

Jim Mercer <jim@reptiles.org> writes:
> it appears that each call to UPDATE seems to be taking a long, long time to
> complete.

Poor choice of plan, maybe?  What does EXPLAIN say about how a typical
example of the UPDATE will be executed?

            regards, tom lane

On Fri, Jun 23, 2000 at 12:00:31PM -0400, Tom Lane wrote:
> Jim Mercer <jim@reptiles.org> writes:
> > it appears that each call to UPDATE seems to be taking a long, long time to
> > complete.
>
> Poor choice of plan, maybe?  What does EXPLAIN say about how a typical
> example of the UPDATE will be executed?

silly me, i should have know to do more investigation before going to the list.

as it turns out, the index on the key was not being used.

a "vacuum verbose analyze" caused things to run much, much faster.
(hence the UPDATE ... WHERE key = ... was taking extra long)

--
[ Jim Mercer                 jim@reptiles.org              +1 416 410-5633 ]
[          Reptilian Research -- Longer Life through Colder Blood          ]
[  Don't be fooled by cheap Finnish imitations; BSD is the One True Code.  ]