Re: SELECT INTO large FKyed table is slow - Mailing list pgsql-performance
From | Mario Splivalo |
---|---|
Subject | Re: SELECT INTO large FKyed table is slow |
Date | |
Msg-id | 4CF39B57.9030306@megafon.hr Whole thread Raw |
In response to | Re: SELECT INTO large FKyed table is slow ("Pierre C" <lists@peufeu.com>) |
Responses |
Re: SELECT INTO large FKyed table is slow
|
List | pgsql-performance |
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
pgsql-performance by date: