extended stats not friendly towards ANALYZE with subset of columns

From: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
Subject: extended stats not friendly towards ANALYZE with subset of columns
Date: 2017-03-28 09:30:03
Message-ID: CAKJS1f9Kk0NF6Fg7TA=JUXsjpS9kX6NVu27pb5QDCpOYAvb-Og@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
stats_ext_analyze_fix.patch application/octet-stream 5.7 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Arthur Zakirov 2017-03-28 09:58:33 Re: [PATCH] Generic type subscripting
Previous Message Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?= 2017-03-28 09:23:57 Re: [COMMITTERS] pgsql: Clean up Perl code according to perlcritic