Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 52428651774 for ; Thu, 26 Jun 2008 15:47:27 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 40023-02-2 for ; Thu, 26 Jun 2008 15:47:17 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from outmail128170.authsmtp.co.uk (outmail128170.authsmtp.co.uk [62.13.128.170]) by postgresql.org (Postfix) with ESMTP id 2CDB165186D for ; Thu, 26 Jun 2008 15:25:25 -0300 (ADT) Received: from mail-c188.authsmtp.com (mail-c188.authsmtp.com [62.13.128.25]) by punt2.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m5QIPLgK050956; Thu, 26 Jun 2008 19:25:21 +0100 (BST) Received: from [192.168.0.3] (85-211-229-155.dyn.gotadsl.co.uk [85.211.229.155]) (authenticated bits=0) by mail.authsmtp.com (8.14.2/8.14.2/Kp) with ESMTP id m5QIPJTq016620; Thu, 26 Jun 2008 19:25:19 +0100 (BST) Subject: Re: get_relation_stats_hook() From: Simon Riggs To: Tom Lane Cc: Alvaro Herrera , pgsql-hackers In-Reply-To: <17759.1214499056@sss.pgh.pa.us> References: <1214490037.3845.136.camel@ebony.site> <16415.1214493494@sss.pgh.pa.us> <1214496170.3845.196.camel@ebony.site> <17759.1214499056@sss.pgh.pa.us> Content-Type: text/plain Date: Thu, 26 Jun 2008 19:29:53 +0100 Message-Id: <1214504993.3845.235.camel@ebony.site> Mime-Version: 1.0 X-Mailer: Evolution 2.12.0 Content-Transfer-Encoding: 7bit X-Server-Quench: 3bc24174-43ad-11dd-aecc-001871e930f4 X-AuthRoute: OCdxZQATClZOTQEd DAteCiNZVAwpPBRK HVkIKg5MJUcNSQVJ NksadRtFagNbZ0xf HGQLW1xEUVR7W2Z/ aQofZQBDYEtPQQdo WVZIR1dNFgBqBAMB SEMbM2ASDWEHeH92 Y0RiEHJfVUQ0dhQu QEpVEmkbYWQxPH0e URQMalIBI1BXfh9E Yld+AXoOYTMDKBg1 TUcAHxkaHhhlExEd TQwRGkgfRU8XHzk6 cAseFCk0Kk0CQCZz bnQn X-Authentic-SMTP: 61633235383639.cat.dmpriest.net.uk:1078/Kp X-Report-SPAM: If SPAM / abuse - report it at: http://www.authsmtp.com/abuse X-Virus-Status: No virus detected - but ensure you scan with your own anti-virus system! X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200806/1004 X-Sequence-Number: 120117 On Thu, 2008-06-26 at 12:50 -0400, Tom Lane wrote: > Simon Riggs writes: > >> Surely you didn't mean ALL calls. Please be more specific about what > >> you're proposing. > > > The statistics relation STATRELATT is accessed in a few places in the > > planner. Since it is in the syscache it is accessed directly from there. > > I would like to add hooks so that stats data can come from somewhere > > else other than the syscache for tables, just as we can already do with > > get_relation_stats_hook(). > > Well, defining the hooks as replacing STATRELATT lookups seems the wrong > level of abstraction. What if you want to insert stats that can't be > represented by the current pg_statistic definition? Even if there's no > functional limitation, cons'ing up a dummy pg_statistic tuple seems the > hard way to do it --- we didn't define get_relation_info_hook as working > by supplying made-up catalog rows. Furthermore, many of the interesting > cases that someone might want to hook into are code paths that don't > even try to look up a pg_statistic row because they know there won't be > one, such as two out of the three cases in examine_variable(). The reason for doing it this way is I'm interested in using stats literally copied from other servers. So the pg_statistic tuples will be available for us directly. I'm building a tool to allow people to export their production environment to a test system, so that SQL developers can experiment with query tuning and optimizer developers can recreate problems. > I think you need to move up a level, and perhaps refactor some of the > existing code to make it easier to inject made-up stats. Both sound like good ideas. I wasn't really after ultimate flexibility, but perhaps I should be. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support