Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes
To: "Mario Splivalo" <mario.splivalo@megafon.hr>
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>
Date: Mon, 29 Nov 2010 17:47:49 +0100
MIME-Version: 1.0
Content-Transfer-Encoding: 8bit
From: "Pierre C" <lists@peufeu.com>
Message-ID: <op.vmxx9zpreorkce@apollo13>
In-Reply-To: <4CF39B57.9030306@megafon.hr>
User-Agent: Opera Mail/10.62 (Linux)

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