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 |
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 |