A-Original-Client-UID: 188.129.73.52 splivalo
Message-ID: <4CF39B57.9030306@megafon.hr>
Date: Mon, 29 Nov 2010 13:23:51 +0100
From: Mario Splivalo <mario.splivalo@megafon.hr>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.15) Gecko/20101027 Lightning/1.0b1 Thunderbird/3.0.10
MIME-Version: 1.0
To: Pierre C <lists@peufeu.com>
CC: pgsql-performance@postgresql.org
Subject: Re: SELECT INTO large FKyed table is slow
References: <4CF24103.8030209@megafon.hr> <op.vmv9j3oneorkce@apollo13>
	<4CF2A8A6.4010400@megafon.hr> <op.vmwhmsepeorkce@apollo13>
In-Reply-To: <op.vmwhmsepeorkce@apollo13>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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