Re: Slow Query

>>> On Mon, Sep 3, 2007 at  6:53 PM, in message
<20070903165334.0da769c1@boffin.xmtservices.net>, Shawn
<postgres@xmtservices.net> wrote:
> vacuum verbose analyze shawns_data;
> INFO:  vacuuming "public.shawns_data"
> INFO:  scanned index "shawns_data_pkey" to remove 21444 row versions
> DETAIL:  CPU 0.24s/0.12u sec elapsed 8.35 sec.
> INFO:  scanned index "sd_l" to remove 21444 row versions
> DETAIL:  CPU 0.32s/0.16u sec elapsed 6.11 sec.
> INFO:  scanned index "sd_b" to remove 21444 row versions
> DETAIL:  CPU 0.34s/0.13u sec elapsed 10.10 sec.
> INFO:  scanned index "sd_s" to remove 21444 row versions
> DETAIL:  CPU 0.36s/0.13u sec elapsed 7.16 sec.
> INFO:  scanned index "sd_e" to remove 21444 row versions
> DETAIL:  CPU 0.40s/0.17u sec elapsed 6.71 sec.
> INFO:  scanned index "sd_alias_hash" to remove 21444 row versions
> DETAIL:  CPU 0.00s/0.01u sec elapsed 0.01 sec.
> INFO:  "shawns_data": removed 21444 row versions in 513 pages
> DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  index "shawns_data_pkey" now contains 15445 row versions in
> 35230 pages DETAIL:  21444 index row versions were removed.
> 19255 index pages have been deleted, 19255 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  index "sd_l" now contains 15445 row versions in 32569 pages
> DETAIL:  21444 index row versions were removed.
> 18059 index pages have been deleted, 18059 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  index "sd_b" now contains 15445 row versions in 34119 pages
> DETAIL:  21444 index row versions were removed.
> 30276 index pages have been deleted, 30219 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  index "sd_s" now contains 15445 row versions in 35700 pages
> DETAIL:  21444 index row versions were removed.
> 31284 index pages have been deleted, 31233 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  index "sd_e" now contains 15445 row versions in 42333 pages
> DETAIL:  21444 index row versions were removed.
> 28828 index pages have been deleted, 28820 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  index "sd_alias_hash" now contains 10722 row versions in 298
> pages DETAIL:  10722 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.00s/0.00u sec elapsed 0.00 sec.
> INFO:  "shawns_data": found 21444 removable, 15445 nonremovable row
> versions in 770 pages DETAIL:  0 dead row versions cannot be removed
> yet. There were 5825 unused item pointers.
> 543 pages contain useful free space.
> 0 pages are entirely empty.
> CPU 1.68s/0.77u sec elapsed 38.47 sec.

Those indexes are killing you.  Hopefully you realize that each of those
indexes will have a new entry inserted whenever you update a row.  If your
indexes are that expensive to maintain, you want to go out of your way
update rows only when something actually changes, which is not the case
for your second update statement yet.

I don't recall seeing the table definition yet.  Could we see that, with
the indexes?  Also, have you tried that CLUSTER yet?  Syntax:

CLUSTER shawns_data_pkey ON shawns_data;
ANALYZE shawns_data;
(or VACUUM ANALYZE)

This will clean up index bloat.

-Kevin