Re: Updates are slow.. - Mailing list pgsql-general

Well, interesting developments - mostly our error. I'm going to
overkill on information just to be thorough (and because I
already collected it :).

Fran Fabrizio had an extremely helpful suggestion:
> No idea if this would help but try REINDEX TABLE EMP; beforehand too.
>  VACUUM ANALYZE does not free space from indexes; only tables.  I just
> discovered this last month after a year of using Pg.

After trying this, the time was cut from 40 minutes to 20 minutes,
which is a huge improvement but still quite slow.

Tom, many thanks for testing it out.
Here are the answers to your questions.

> What are the datatypes of your columns, exactly?

Because we've solved the problem, I'll just cut it down
to the columns we have data in:

         Column          |           Type
-------------------------+--------------------------
 emp_id                  | integer                  | not null
 email                   | character varying(255)   |
 emp_code                | character varying(50)    |
 created_dt              | timestamp with time zone | not null
 fk_emp_status_id        | smallint                 | not null
 fk_dept_id              | integer                  | not null
 fk_role_id              | integer                  |

We actually have 10 more columns, but i no longer think it's
relevant.

> What's the average
> tuple size (actually, showing the VACUUM VERBOSE stats for the table
> would be the most useful answer)?

eppend=# vacuum verbose clientdata;
NOTICE:  --Relation clientdata--
NOTICE:  Index emp_pk: Pages 4018; Tuples 1230703: Deleted 597153.
        CPU 0.87s/9.20u sec elapsed 15.27 sec.
NOTICE:  Index emp_ix01: Pages 5445; Tuples 1230703: Deleted 597153.
        CPU 0.96s/6.62u sec elapsed 17.83 sec.
NOTICE:  Index emp_ix02: Pages 13972; Tuples 1230703: Deleted 597153.
        CPU 2.73s/8.95u sec elapsed 78.56 sec.
NOTICE:  Removed 1194306 tuples in 16048 pages.
        CPU 3.28s/2.91u sec elapsed 21.83 sec.
NOTICE:  Pages 32576: Changed 16469, Empty 0; Tup 1230703: Vac 1194306,
Keep 0, UnUsed 5501.
        Total CPU 11.32s/28.43u sec elapsed 155.81 sec.
VACUUM

The vacuum made me realize that there was an index I had forgotten
about. emp_pk is (emp_id) and emp_ix01 is (fk_dept_id, fk_emp_status_id).
More on emp_ix02 in a second, which is the real cause of the problem.

> Are you *sure* there are no foreign
> keys either from or to this table?

Yes, actually we've dropped all other tables for simplicity of the test.
This is the only table in the database.

> Also, what plan is shown by EXPLAIN for the query?
>             regards, tom lane

This explain analyse I ran took some 20+ minutes itself.

eppend=# explain analyse
eppend-# update emp
eppend-# set fk_dept_id = 5
eppend-# where fk_dept_id= 4;
NOTICE:  QUERY PLAN:

Seq Scan on clientdata  (cost=0.00..47959.79 rows=915643 width=522)
(actual time=1764.06..362296.23 rows=597153 loops=1)
Total runtime: 1441799.02 msec

EXPLAIN

> It would be also a good idea if you could send us the output of your
> system "ipcs" command so we can get an idea of the amount of shared
> memory you are using.
>
> - Ericson Smith

bash-2.05$ ipcs

------ Shared Memory Segments --------
key        shmid      owner      perms      bytes      nattch   status
0x0052e2c1 0          postgres  600        68878336   4
0x00000000 163841     root      777        196608     2         dest
0x00000000 851970     root      777        196608     2         dest
0x00000000 1900547    root      777        196608     2         dest
0x00000000 2031620    root      777        196608     2         dest

------ Semaphore Arrays --------
key        semid      owner      perms      nsems      status
0x0052e2c1 0          postgres  600        17
0x0052e2c2 32769      postgres  600        17
0x0052e2c3 65538      postgres  600        17

------ Message Queues --------
key        msqid      owner      perms      used-bytes   messages

However, the real kicker is that I looked up what the emp_ix02 index
was and it was (email, fk_dept_id). Therefore, there were actually
two indexes on this column that had to be updated, and this index
had a lot more pages. When I dropped this index and re-ran the updated
- it took only 2 minutes!

So with the combination of the REINDEX and dropping a troublesome
index (we'll recreate the email index without dept_id), we're down
to a good time.  I'm hoping reindex will help with some of the
other slow queries we've seen.

Thanks again for everyone's help!

Tom Burke
Eppend, Inc.
http://www.eppend.com/