Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WAqUc-0007Kb-H5 for pgsql-performance@arkaria.postgresql.org; Wed, 05 Feb 2014 00:37:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WAqUc-0000O9-0F for pgsql-performance@arkaria.postgresql.org; Wed, 05 Feb 2014 00:37:30 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WAOia-0007jr-Tl for pgsql-performance@postgresql.org; Mon, 03 Feb 2014 18:58:05 +0000 Received: from mail3out1.superhost.pl ([2a02:1778::1:82] helo=mail-out3-4.superhost.pl) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WAOiV-0004fG-Sf for pgsql-performance@postgresql.org; Mon, 03 Feb 2014 18:58:04 +0000 Received: from ArturW7 (5-226-122-185.ip.netia.com.pl [5.226.122.185]) (Authenticated sender: azajac@cfi.pl) by afrodyta3.superhost.pl (Postfix) with ESMTPA id B960818384C3 for ; Mon, 3 Feb 2014 19:57:54 +0100 (CET) From: =?iso-8859-2?Q?Artur_Zaj=B1c_CFI?= To: Subject: Planner estimates and VACUUM/autovacuum Date: Mon, 3 Feb 2014 19:57:45 +0100 Message-ID: <033801cf2111$d3518750$79f495f0$@cfi.pl> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0339_01CF211A.35183940" X-Mailer: Microsoft Outlook 15.0 Thread-Index: Ac8hDE86CkEGgtsIRNO8rcveM7EL0A== Content-Language: pl X-Pg-Spam-Score: 1.6 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org This is a multipart message in MIME format. ------=_NextPart_000_0339_01CF211A.35183940 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit Hi, I am not sure if it is bug or not but I found some strange behaviour. Maybe it is the same as described on http://www.postgresql.org/message-id/14616.1244317490@sss.pgh.pa.us ?). If yes - I'm sorry for the trouble, but I think that my example is more obvious. Tested on PostgreSQL 9.2.4 and 9.2.6. Console 1: BEGIN; DECLARE a CURSOR FOR SELECT * FROM tab; --- Keep cursor open for disallow full vacuum of tab Console 2: SELECT count(*) FROM tab; ---- Result: 3588; select reltuples from pg_class where relname='table'; --- Result: 3588 UPDATE tab SET id=id; UPDATE tab SET id=id; UPDATE tab SET id=id; VACUUM ANALYZE tab; select reltuples from pg_class where relname='table'; --- Result: 3588 Now wait few seconds :) select reltuples from pg_class where relname='table'; --- Result: 12560 VACUUM ANALYZE tab; select reltuples from pg_class where relname='table'; --- Result: 3588 There is 3588 live records and 12560 live+dead records in table. That is strange for me. VACUUM updates pg_class.reltuples differently (only live roiws count) than autovacuum (live and dead rows). Why? Also in planning: explain SELECT id FROM tab; QUERY PLAN ---------------------------------------------------------------------- Seq Scan on tab (cost=0.00..1074.60 rows=12560 width=4) Estimation is done with the use of current pg_class.reltuples value. This value includes dead rows count after autovacuum so estimation is bad, especially in more complex planner tree, for example: Explain SELECT a.id FROM tab AS a JOIN tab AS b USING (id); QUERY PLAN ---------------------------------------------------------------------------- ----------------------------- Nested Loop (cost=0.00..6410.70 rows=12560 width=4) -> Seq Scan on tab a (cost=0.00..1074.60 rows=12560 width=8) -> Index Only Scan using tab_pkey on tab b (cost=0.00..0.41 rows=1 width=4) Index Cond: (id = a.id) PostgreSQL estimates 12560 records in query result. This is wrong estimation if dead tuples are removed during seq scan or index scan (I suppose that it is). I don't think that AUTOVACUUM and VACUUM ANALYZE should behave differently :( -------------------------------------------------------------------------- Artur Zajac ------=_NextPart_000_0339_01CF211A.35183940 Content-Type: text/html; charset="iso-8859-2" Content-Transfer-Encoding: quoted-printable

Hi,

 

I am not sure if it is bug or not but I found some strange = behaviour. Maybe it is the same as described on http://www.postgresql.org/message-id/14616.1244317490@sss.pgh.pa.us ?). If yes – I’m sorry for the trouble, but I think that = my example is more obvious.

 

Tested on PostgreSQL 9.2.4 and = 9.2.6.

 

Console 1:

BEGIN;

DECLARE a CURSOR FOR SELECT * FROM = tab;

--- = Keep cursor open for disallow full vacuum of tab

 

Console 2:

SELECT count(*) FROM = tab;=A0=A0=A0=A0

---- Result: 3588;

select reltuples from pg_class = where relname=3D'table’;=A0

--- Result: = 3588

UPDATE = tab SET id=3Did;

UPDATE tab SET id=3Did;

UPDATE tab SET = id=3Did;

VACUUM ANALYZE tab;

select reltuples from pg_class = where relname=3D'table’;

--- Result: = 3588

 

Now wait few seconds J

 

select reltuples from pg_class where = relname=3D'table’;

--- Result: = 12560

 

VACUUM ANALYZE tab;

select reltuples from pg_class = where relname=3D'table’;

--- Result: = 3588

 

There is 3588 live records and 12560 live+dead records in = table.

That = is strange for me. VACUUM updates pg_class.reltuples differently (only = live roiws count) than autovacuum (live and dead rows). = Why?

 

Also in planning:

 

explain SELECT id FROM = tab;

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN

------------------------------------------------------------= ----------

= Seq Scan on tab=A0 (cost=3D0.00..1074.60 rows=3D12560 = width=3D4)

 

Estimation is done with the use of current = pg_class.reltuples value. =A0This value includes dead rows count after = autovacuum so estimation is bad, especially in more complex planner = tree, for example:

 

Explain SELECT a.id FROM tab AS a JOIN tab AS b USING = (id);

 

=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0 QUERY PLAN

------------------------------------------------------------= ---------------------------------------------

Nested Loop=A0 = (cost=3D0.00..6410.70 rows=3D12560 width=3D4)

=A0=A0 ->=A0 Seq Scan on tab = a=A0 (cost=3D0.00..1074.60 rows=3D12560 = width=3D8)

=A0=A0 ->=A0 Index Only Scan using tab_pkey on tab b=A0 = (cost=3D0.00..0.41 rows=3D1 width=3D4)

=A0=A0=A0=A0=A0=A0=A0=A0 Index = Cond: (id =3D a.id)

 

PostgreSQL estimates 12560 records in query result. This is = wrong estimation if dead tuples are removed during seq scan or index = scan (I suppose that it is).

 

I don’t think that AUTOVACUUM = and VACUUM ANALYZE should behave differently L

 

---------------------------------------= -----------------------------------

Artur Zajac

 

------=_NextPart_000_0339_01CF211A.35183940--