Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M255620=pgsql-hackers-arkari@postgresql.org>)
 id 1XKexD-0008VS-GK
 for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Aug 2014 02:51:51 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-hackers-owner+M255620=pgsql-hackers-arkari@postgresql.org>)
 id 1XKexD-0002Sy-0L
 for pgsql-hackers@arkaria.postgresql.org; Fri, 22 Aug 2014 02:51:51 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256)
 (Exim 4.80) (envelope-from <noah@leadboat.com>) id 1XKexB-0002So-M3
 for pgsql-hackers@postgresql.org; Fri, 22 Aug 2014 02:51:49 +0000
Received: from mail-pa0-f66.google.com ([209.85.220.66])
 by makus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256)
 (Exim 4.80) (envelope-from <noah@leadboat.com>) id 1XKex4-0007t5-BU
 for pgsql-hackers@postgresql.org; Fri, 22 Aug 2014 02:51:47 +0000
Received: by mail-pa0-f66.google.com with SMTP id lf10so7829897pab.9
 for <pgsql-hackers@postgresql.org>; Thu, 21 Aug 2014 19:51:40 -0700 (PDT)
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
 d=1e100.net; s=20130820;
 h=x-gm-message-state:date:from:to:cc:subject:message-id:references
 :mime-version:content-type:content-disposition:in-reply-to
 :user-agent;
 bh=URPJTRmFUUYFoIP+VgZAk7pRjTC7y91fCGWPQJKsFPQ=;
 b=a19A5t4nhDCbB03oBaek/6WXD+WoeGDMMlwa+0h3LfiN1uT/mEboj3sGxT0UDbAQi1
 nyePjdsJLFQnYPKLI4F1/u5d81sRIcuwk0dtJBCm6yOmk8k2dPzD+LRwBbmBV7gqYzU4
 Azb72mmcSSbk1MWzQEBeA/v4JhDEDA16zA0qcougapRQgxLuNuSJ9g3sy92l690lbrjY
 lHrObguSM9Fiqiu40hRw2DXdaqX/kN5bUBqCNPCy8LnXoNulS1bjvEgc15HfFzlqDk44
 vPFSd0jzhJh9ziQiRCtZ4CRlDnkgU+kYjSQZ9Mm1aEO49YktyNRul4AVMch+3et9sZZ0
 KHVA==
X-Gm-Message-State: 
 ALoCoQmb+h3U9YwANucrFmQZQrprn/kNNag6bpUXq1HoGm7LgR44+PIMiSa3eWh6/9fDJjLRulZn
X-Received: by 10.70.90.48 with SMTP id bt16mr2913903pdb.72.1408675900557;
 Thu, 21 Aug 2014 19:51:40 -0700 (PDT)
Received: from tornado.leadboat.com (c-174-62-109-97.hsd1.ca.comcast.net.
 [174.62.109.97])
 by mx.google.com with ESMTPSA id tb6sm1812263pbc.78.2014.08.21.19.51.39
 for <multiple recipients>
 (version=TLSv1 cipher=RC4-SHA bits=128/128);
 Thu, 21 Aug 2014 19:51:39 -0700 (PDT)
Date: Thu, 21 Aug 2014 22:51:37 -0400
From: Noah Misch <noah@leadboat.com>
To: Etsuro Fujita <fujita.etsuro@lab.ntt.co.jp>
Cc: shigeru.hanada@gmail.com,
 Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>,
 pgsql-hackers@postgresql.org
Subject: Re: inherit support for foreign tables
Message-ID: <20140822025137.GA563733@tornado.leadboat.com>
References: <53393E18.3080901@lab.ntt.co.jp> <533C01C7.6030809@lab.ntt.co.jp>
 <53706FB6.6000403@lab.ntt.co.jp>
 <20140620.170406.123403564.horiguchi.kyotaro@lab.ntt.co.jp>
 <20140702022327.GC1586927@tornado.leadboat.com>
 <53F48245.6020204@lab.ntt.co.jp>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <53F48245.6020204@lab.ntt.co.jp>
User-Agent: Mutt/1.5.21 (2010-09-15)
X-Pg-Spam-Score: -2.6 (--)
List-Archive: <http://archives.postgresql.org/pgsql-hackers>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-hackers.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@postgresql.org>
List-Post: <mailto:pgsql-hackers@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-hackers>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
X-Mailing-List: pgsql-hackers
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On Wed, Aug 20, 2014 at 08:11:01PM +0900, Etsuro Fujita wrote:
> (2014/07/02 11:23), Noah Misch wrote:
> >Your chosen ANALYZE behavior is fair, but the messaging from a database-wide
> >ANALYZE VERBOSE needs work:
> >
> >INFO:  analyzing "test_foreign_inherit.parent"
> >INFO:  "parent": scanned 1 of 1 pages, containing 1 live rows and 0 dead rows; 1 rows in sample, 1 estimated total rows
> >INFO:  analyzing "test_foreign_inherit.parent" inheritance tree
> >WARNING:  relcache reference leak: relation "child" not closed
> >WARNING:  relcache reference leak: relation "tchild" not closed
> >WARNING:  relcache reference leak: relation "parent" not closed
> >
> >Please arrange to omit the 'analyzing "tablename" inheritance tree' message,
> >since this ANALYZE actually skipped that task.  (The warnings obviously need a
> >fix, too.)  I do find it awkward that adding a foreign table to an inheritance
> >tree will make autovacuum stop collecting statistics on that inheritance tree,
> >but I can't think of a better policy.
> 
> I think it would be better that this is handled in the same way as
> an inheritance tree that turns out to be a singe table that doesn't
> have any descendants in acquire_inherited_sample_rows().  That would
> still output the message as shown below, but I think that that would
> be more consistent with the existing code.  The patch works so.
> (The warnings are also fixed.)
> 
> INFO:  analyzing "public.parent"
> INFO:  "parent": scanned 0 of 0 pages, containing 0 live rows and 0
> dead rows; 0 rows in sample, 0 estimated total rows
> INFO:  analyzing "public.parent" inheritance tree
> INFO:  analyzing "pg_catalog.pg_authid"
> INFO:  "pg_authid": scanned 1 of 1 pages, containing 1 live rows and
> 0 dead rows; 1 rows in sample, 1 estimated total rows

Today's ANALYZE VERBOSE messaging for former inheritance parents (tables with
relhassubclass = true but no pg_inherits.inhparent links) is deceptive, and I
welcome a fix to omit the spurious message.  As defects go, this is quite
minor.  There's fundamentally no value in collecting inheritance tree
statistics for such a parent, and no PostgreSQL command will do so.

Your proposed behavior for inheritance parents having at least one foreign
table child is more likely to mislead DBAs in practice.  An inheritance tree
genuinely exists, and a different ANALYZE command is quite capable of
collecting statistics on that inheritance tree.  Messaging should reflect the
difference between ANALYZE invocations that do such work and ANALYZE
invocations that skip it.  I'm anticipating a bug report along these lines:

  I saw poor estimates involving a child foreign table, so I ran "ANALYZE
  VERBOSE", which reported 'INFO:  analyzing "public.parent" inheritance
  tree'.  Estimates remained poor, so I scratched my head for awhile before
  noticing that pg_stats didn't report such statistics.  I then ran "ANALYZE
  VERBOSE parent", saw the same 'INFO:  analyzing "public.parent" inheritance
  tree', but this time saw relevant rows in pg_stats.  The message doesn't
  reflect the actual behavior.

I'll sympathize with that complaint.  It's a minor point overall, but the code
impact is, I predict, small enough that we may as well get it right.  A
credible alternative is to emit a second message indicating that we skipped
the inheritance tree statistics after all, and why we skipped them.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers