realm_51=# vacuum analyze verbose drones;
INFO: vacuuming "public.drones"
INFO: scanned index "drones_pk" to remove 242235 row versions
DETAIL: CPU 0.02s/0.11u sec elapsed 0.28 sec.
INFO: "drones": removed 242235 row versions in 1952 pages
DETAIL: CPU 0.01s/0.02u sec elapsed 0.03 sec.
INFO: index "drones_pk" now contains 174068 row versions in 721 pages
DETAIL: 107716 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
As you can see your index contains 174068 active rows and 242235 dead rows
that probably should have been removed a long time ago by autovacuum, but
you seem to have it turned off. It does not take a long time to vacuum
this table (only 0.3 sec) so it is not a high cost, you should enable
autovacuum and let it do the job (note that this doesn't stop you from
manual vacuuming after big updates).
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "drones": found 486 removable, 174068 nonremovable row versions
in 1958 out of 1958 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 64 unused item pointers.
0 pages are entirely empty.
CPU 0.22s/0.90u sec elapsed 22.29 sec.
Here, the table itself seems quite normal... strange.
INFO: vacuuming "pg_toast.pg_toast_2695558"
INFO: index "pg_toast_2695558_index" now contains 0 row versions in 1
pages
DETAIL: 0 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: "pg_toast_2695558": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
Since you don't have large fields, the toast table is empty...
realm_51=# vacuum analyze verbose drones_history;
INFO: vacuuming "public.drones_history"
INFO: index "drones_history_pk" now contains 25440352 row versions in
69268 pages
DETAIL: 0 index row versions were removed.
good
0 index pages have been deleted, 0 are currently reusable.
CPU 0.38s/0.12u sec elapsed 16.56 sec.
INFO: "drones_history": found 0 removable, 16903164 nonremovable row
versions in 129866 out of 195180 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 2.00s/1.42u sec elapsed 49.24 sec.
good
INFO: vacuuming "pg_toast.pg_toast_2695510"
INFO: index "pg_toast_2695510_index" now contains 0 row versions in 1
pages
DETAIL: 0 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: "pg_toast_2695510": found 0 removable, 0 nonremovable row
versions in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
same as above, no toast
realm_51=# select version();
version
---------------------------------------------------------------------------------------------
PostgreSQL 8.4.2 on i686-pc-linux-gnu, compiled by GCC gcc (Debian
4.3.2-1.1) 4.3.2, 32-bit
(1 row)
Mario
ok
Try this :
CLUSTER drones_pkey ON drones;
Then check if your slow query gets a bit faster. If it does, try :
ALTER TABLE drones SET ( fillfactor = 50 );
ALTER INDEX drones_pkey SET ( fillfactor = 50 );
CLUSTER drones_pkey ON drones; (again)
This will make the updates on this table less problematic. VACUUM it after
each mass update.
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
