Horton

Thread

Commits

  1. Fix extended statistics with partial analyzes

  1. extended stats not friendly towards ANALYZE with subset of columns

    David Rowley <david.rowley@2ndquadrant.com> — 2017-03-28T09:30:03Z

    I'm just reviewing Tomas' code for the dependencies part of the stats
when I saw something that looked a bit unusual.

I tested with:

CREATE TABLE ab1 (a INTEGER, b INTEGER);
ALTER TABLE ab1 ALTER a SET STATISTICS 0;
INSERT INTO ab1 SELECT a, a%23 FROM generate_series(1, 1000) a;
CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
ANALYZE ab1;

And got:

ERROR:  extended statistics could not be collected for column "a" of
relation public.ab1
HINT:  Consider ALTER TABLE "public"."ab1" ALTER "a" SET STATISTICS -1

I don't think the error is useful here, as it means nothing gets done.
Probably better to just not (re)build those stats.

Another option would be to check for extended stats before deciding
which rows to ANALYZE, then still gathering the columns required for
MV stats, but I think if the user asks for a subset of columns to be
analyzed, and that causes a column to be missing for an extended
statistics, that it would be pretty surprising if we rebuild the
extended stats.

Perhaps the SET STATISTIC 0 for a column still needs to gather data
for extended statistics, though. Perhaps a debate should ensue about
how that should work exactly.

I've attached a patch which fixes the problem above, but it does
nothing to change the analyze behaviour for 0 statistics columns.


-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  2. Re: extended stats not friendly towards ANALYZE with subset of columns

    Noah Misch <noah@leadboat.com> — 2017-04-05T06:55:18Z

    On Tue, Mar 28, 2017 at 10:30:03PM +1300, David Rowley wrote:
> I'm just reviewing Tomas' code for the dependencies part of the stats
> when I saw something that looked a bit unusual.
> 
> I tested with:
> 
> CREATE TABLE ab1 (a INTEGER, b INTEGER);
> ALTER TABLE ab1 ALTER a SET STATISTICS 0;
> INSERT INTO ab1 SELECT a, a%23 FROM generate_series(1, 1000) a;
> CREATE STATISTICS ab1_a_b_stats ON (a, b) FROM ab1;
> ANALYZE ab1;
> 
> And got:
> 
> ERROR:  extended statistics could not be collected for column "a" of
> relation public.ab1
> HINT:  Consider ALTER TABLE "public"."ab1" ALTER "a" SET STATISTICS -1
> 
> I don't think the error is useful here, as it means nothing gets done.
> Probably better to just not (re)build those stats.
> 
> Another option would be to check for extended stats before deciding
> which rows to ANALYZE, then still gathering the columns required for
> MV stats, but I think if the user asks for a subset of columns to be
> analyzed, and that causes a column to be missing for an extended
> statistics, that it would be pretty surprising if we rebuild the
> extended stats.
> 
> Perhaps the SET STATISTIC 0 for a column still needs to gather data
> for extended statistics, though. Perhaps a debate should ensue about
> how that should work exactly.
> 
> I've attached a patch which fixes the problem above, but it does
> nothing to change the analyze behaviour for 0 statistics columns.

[Action required within three days.  This is a generic notification.]

The above-described topic is currently a PostgreSQL 10 open item.  Álvaro,
since you committed the patch believed to have created it, you own this open
item.  If some other commit is more relevant or if this does not belong as a
v10 open item, please let us know.  Otherwise, please observe the policy on
open item ownership[1] and send a status update within three calendar days of
this message.  Include a date for your subsequent status update.  Testers may
discover new open items at any time, and I want to plan to get them all fixed
well in advance of shipping v10.  Consequently, I will appreciate your efforts
toward speedy resolution.  Thanks.

[1] https://www.postgresql.org/message-id/20170404140717.GA2675809%40tornado.leadboat.com

  3. Re: Re: extended stats not friendly towards ANALYZE with subset of columns

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2017-04-05T11:58:54Z

    Noah Misch wrote:

> The above-described topic is currently a PostgreSQL 10 open item.  Álvaro,
> since you committed the patch believed to have created it, you own this open
> item.

I'll commit a fix for this problem no later than Friday 14th.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  4. Re: Re: extended stats not friendly towards ANALYZE with subset of columns

    Noah Misch <noah@leadboat.com> — 2017-04-16T04:25:31Z

    On Wed, Apr 05, 2017 at 08:58:54AM -0300, Alvaro Herrera wrote:
> Noah Misch wrote:
> > The above-described topic is currently a PostgreSQL 10 open item.  Álvaro,
> > since you committed the patch believed to have created it, you own this open
> > item.
> 
> I'll commit a fix for this problem no later than Friday 14th.

This PostgreSQL 10 open item is past due for your status update.  Kindly send
a status update within 24 hours, and include a date for your subsequent status
update.  Refer to the policy on open item ownership:
https://www.postgresql.org/message-id/20170404140717.GA2675809%40tornado.leadboat.com

  5. Re: Re: extended stats not friendly towards ANALYZE with subset of columns

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2017-04-17T14:47:40Z

    Noah Misch wrote:
> On Wed, Apr 05, 2017 at 08:58:54AM -0300, Alvaro Herrera wrote:
> > Noah Misch wrote:
> > > The above-described topic is currently a PostgreSQL 10 open item.  Álvaro,
> > > since you committed the patch believed to have created it, you own this open
> > > item.
> > 
> > I'll commit a fix for this problem no later than Friday 14th.
> 
> This PostgreSQL 10 open item is past due for your status update.  Kindly send
> a status update within 24 hours, and include a date for your subsequent status
> update.  Refer to the policy on open item ownership:
> https://www.postgresql.org/message-id/20170404140717.GA2675809%40tornado.leadboat.com

I'm working on the patch currently and intend to get it pushed no later
than tomorrow 19:00 America/Santiago.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  6. Re: extended stats not friendly towards ANALYZE with subset of columns

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2017-04-17T17:12:23Z

    David Rowley wrote:

> ERROR:  extended statistics could not be collected for column "a" of
> relation public.ab1
> HINT:  Consider ALTER TABLE "public"."ab1" ALTER "a" SET STATISTICS -1
> 
> I don't think the error is useful here, as it means nothing gets done.
> Probably better to just not (re)build those stats.

Agreed, an error there is a bad idea.

> Another option would be to check for extended stats before deciding
> which rows to ANALYZE, then still gathering the columns required for
> MV stats, but I think if the user asks for a subset of columns to be
> analyzed, and that causes a column to be missing for an extended
> statistics, that it would be pretty surprising if we rebuild the
> extended stats.

That would be very surprising indeed.

> Perhaps the SET STATISTIC 0 for a column still needs to gather data
> for extended statistics, though. Perhaps a debate should ensue about
> how that should work exactly.

Hmm.  I'd rather throw a warning at either CREATE STATISTICS time or at
ALTER TABLE SET STATISTICS time, rather than magically changing the set
of columns at analyze time.

> I've attached a patch which fixes the problem above, but it does
> nothing to change the analyze behaviour for 0 statistics columns.

Pushed, after tweaking so that a WARNING is still emitted, because it's
likely to be useful in pointing out a procedural mistake while extended
stats are being tested.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

  7. Re: extended stats not friendly towards ANALYZE with subset of columns

    David Rowley <david.rowley@2ndquadrant.com> — 2017-04-17T21:59:34Z

    On 18 April 2017 at 05:12, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> Pushed, after tweaking so that a WARNING is still emitted, because it's
> likely to be useful in pointing out a procedural mistake while extended
> stats are being tested.

Thanks for pushing.

Seems you maintained most of my original patch and added to it a bit,
but credits don't seem to reflect that. It's not the end of the world
but just wanted to note that.


-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

  8. Re: extended stats not friendly towards ANALYZE with subset of columns

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2017-04-17T22:22:25Z

    David Rowley wrote:
> On 18 April 2017 at 05:12, Alvaro Herrera <alvherre@2ndquadrant.com> wrote:
> > Pushed, after tweaking so that a WARNING is still emitted, because it's
> > likely to be useful in pointing out a procedural mistake while extended
> > stats are being tested.
> 
> Thanks for pushing.
> 
> Seems you maintained most of my original patch and added to it a bit,
> but credits don't seem to reflect that. It's not the end of the world
> but just wanted to note that.

Yeah, sorry about that.  Maybe we should start mentioning the percentage
of the patch that comes from each author (just kidding).

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services