Hi Alvaro!
On 2019/11/05 22:38, Alvaro Herrera wrote:
> On 2019-Nov-05, Tatsuro Yamada wrote:
>
>> ==============
>> [Session1]
>> \! pgbench -i
>> create statistics pg_ext1 (dependencies) ON aid, bid from pgbench_accounts;
>> create statistics pg_ext2 (mcv) ON aid, bid from pgbench_accounts;
>> create statistics pg_ext3 (ndistinct) ON aid, bid from pgbench_accounts;
>
> Wow, it takes a long time to compute these ...
>
> Hmm, you normally wouldn't define stats that way; you'd do this instead:
>
> create statistics pg_ext1 (dependencies, mcv,ndistinct) ON aid, bid from pgbench_accounts;
I'd like to say it's a just example of test case. But I understand that
your advice. Thanks! :)
> I'm not sure if this has an important impact in practice. What I'm
> saying is that I'm not sure that "number of ext stats" is necessarily a
> useful number as shown. I wonder if it's possible to count the number
> of items that have been computed for each stats object. So if you do
> this
>
> create statistics pg_ext1 (dependencies, mcv) ON aid, bid from pgbench_accounts;
> create statistics pg_ext2 (ndistinct,histogram) ON aid, bid from pgbench_accounts;
>
> then the counter goes to 4. But I also wonder if we need to publish
> _which_ type of ext stats is currently being built, in a separate
> column.
Hmm... I have never seen a lot of extended stats on a table (with many columns)
but I suppose it will be existence near future because extended stats is an only
solution to correct row estimation error in vanilla PostgreSQL. Therefore, it
would be better to add the counter on the view, I think.
I revised the patch as following because I realized counting the types of ext
stats is not useful for users.
- Attached new patch counts a number of ext stats instead the types of ext stats.
So we can see the counter goes to "2", if we created above ext stats (pg_ext1 and
pg_ext2) and analyzed as you wrote. :)
Thanks,
Tatsuro Yamada