On 11/28/2010 10:50 PM, Pierre C wrote:
I pasted DDL at the begining of my post.Ah, sorry, didn't see it ;)The only indexes tables have are the ones created because of PK constraints. Table drones has around 100k rows. Table drones_history has around 30M rows. I'm not sure what additional info you'd want but I'll be more than happy to provide more relevant information.Can you post the following : - pg version - output of VACCUM ANALYZE VERBOSE for your 2 tables
Here it is: 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. 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. 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. INFO: analyzing "public.drones"INFO: "drones": scanned 1958 of 1958 pages, containing 174068 live rows and 0 dead rows; 174068 rows in sample, 174068 estimated total rows
VACUUM 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. 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. 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. INFO: analyzing "public.drones_history"INFO: "drones_history": scanned 195180 of 195180 pages, containing 25440352 live rows and 0 dead rows; 600000 rows in sample, 25440352 estimated total rows
VACUUM 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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
