A-Original-Client-UID: 188.129.71.39 splivalo
Message-ID: <4CF58739.7050904@megafon.hr>
Date: Wed, 01 Dec 2010 00:22:33 +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>
	<4CF39B57.9030306@megafon.hr> <op.vmxx9zpreorkce@apollo13>
In-Reply-To: <op.vmxx9zpreorkce@apollo13>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

On 11/29/2010 05:47 PM, Pierre C wrote:
>> 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).

Yes, you're right. I was doing some testing and I neglected to enable 
vacuuming after inserts. But what this shows is that table drones is 
having dead rows, and that table does get updated a lot. However, I 
don't have any performance problems here. The UPDATE takes no more than 
10 seconds even if I update 50k (out of 150k) rows.

I disabled autovacuum because I got a lot of "WARNING:  pgstat wait 
timeout" and I could see the autovacuum job (pg_stat_activity) running 
during the run of the plpgsql function that handles inserts.

I left the autovacuum off but I do VACUUM after each CSV insert.

> 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

Yes. Just to make things clear, I never update/delete drones_history. I 
just INSERT, and every now and then I'll be doing SELECTs.

>
>
>> 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.

Is this going to make any difference considering slow insert on 
drones_history? Because INSERTs/UPDATEs on drones tables are fast. The 
only noticable difference is that drones is 150k rows 'large' and 
drones_history has around 25M rows:

realm_51=# select count(*) from drones_history ;
   count
----------
  25550475
(1 row)

	Mario