Nikolay: Hello, hello, this is Postgres.FM.
My name is Nikolay, founder of Postgres.AI and as usual my co-host
is Michael, pgMustard.
Hi Michael.
Michael: Hello Nikolay.
Nikolay: How are you doing?
How is your service doing?
I saw a newsletter.
Michael: Oh yes, all good.
Ticking along.
Nikolay: Improvements.
Michael: How about you?
Yeah, also like bug fixes, you know.
Michael: Yeah.
Nikolay: And improvements as well.
We are preparing some big update probably next month.
So I wanted to, actually you wanted to discuss this.
It's your topic.
Michael: Yeah, very much my topic, it sounds like.
Nikolay: Yeah, and I think it's a useful topic, but I also want
to be honest and, as usual, I want to be honest and discuss how
often can we use that feature of Postgres and what's wrong.
Not wrong, maybe, but let's just go like extended statistics
is the topic.
So it's what you can achieve running SQL command, CREATE STATISTICS,
giving it a name.
It's very similar to index creation, right?
Similar, right?
You create something
Michael: with, it's
Nikolay: named for 1 table only.
You cannot do it for multiple tables.
You choose columns, and there are several parameters you can
specify.
And it's very similar to index creation, but instead of building
the whole structure additionally to speed up search of values,
of entries, we just tell Postgres that it needs to maintain more
statistics than usual, because by default it maintains statistics
for every Column individually.
Right?
And also, not only every Column individually, but also multi-column
statistics, it does maintain and also expression, not multi-column,
expression statistics.
It maintains for expression indexes, like Function indexes.
For example, a very simple common case is when we have a Column
named email and we use text, not citext.
Nobody uses citext.
This Extension is not used at all.
I don't know why it exists.
It exists for ages.
citext is an Extension.
It's actually a contrib module, so it's always available in any
Postgres setup.
And it's case-insensitive text.
Because by default, If you create an Index on the Column named
unique Index, it won't guarantee uniqueness in case-insensitive
manner.
So uppercase email, lowercase email, they will be considered
different values.
This is not what we want, because in reality emails, they are
cancel sensitive.
Case doesn't matter.
So you need to use citext contrib module, but nobody does it.
I never see it in reality.
Well, it's so exotic.
I used it last time 10 years ago.
So we usually end up having an Index lower email.
And what I wanted to say is that by default implicitly Postgres
will maintain statistics for this value, lower email.
Right?
Yeah.
So expression.
It's automatically.
But CREATE STATISTICS allows you to maintain additional statistics
like additional expressions or multi-column statistics without
creation of Index?
Michael: Yes, although I've only ever thought until reading a
lot about it earlier today I'd only ever really thought of the
multi-column use cases.
The individual column cases is interesting if you don't if you
if you want to be able to use the statistics on an expression
but don't want the overhead of maintaining an Index on it.
Nikolay: Which
Michael: is interesting but I've not seen that case before.
Nikolay: So for example we have some expression on a Column,
a single Column, we want statistics but we don't have it, we
don't want an Index.
Can you imagine such case?
Michael: I haven't worked out 1 yet, but but it made it into
the documentation.
So I imagine somebody came came with that case 1
Nikolay: example Yeah, I want an example
Michael: But I think I do think it's worth us going back to basics
a little bit, like why do we want or need these statistics?
I think it's really important.
And I was going to just say...
Nikolay: Actually this is what you should see working with individual
query plans in pgMustard, you obviously have cases and as we
discussed before recording, you mentioned that you have some
logic in pgMustard developed to diagnose cases when something
is off and probably you need to consider statistic creation.
Michael: Well yeah, this is the tricky thing.
It's not super intelligent though.
We look out for bad raw estimates and that they can be a real
problem for query plans because the beauty of the Postgres optimizer
is that it's choosing between various plans and it's doing that
work for you.
You don't have to specify how it should get the data, you just
ask your query and it works out, hopefully the fastest way to
do that, but it's making a bunch of decisions about join order,
and maybe that's a bad example for this topic, but a scan type,
different join algorithms, all of these decisions are based largely
on the number of rows being returned at each stage and therefore
the selectivity of various things and calculating those selectivities
is tricky, like it's a hard problem and it's the reason why Postgres
maintains statistics in the first place.
It does that on a per column basis.
The reason it doesn't do it on a combination of column bases
by default is because just the number of those explodes so quickly
like as soon as you've got a few columns in in 1 table the number
of combinations of like is it like n choose 2 like they just
explodes
Nikolay: right
Michael: and So it would be super expensive to do it for everything.
But in certain extreme cases, which are actually quite common
in real world applications, some columns are heavily correlated
to other columns and popular ones and important ones often get
queried together.
So an example I see really often in like software service applications
are things like organization ID and team ID or something like
that you know a team ID is always going to be associated with
an organization ID always always always
Nikolay: or country and language another example
Michael: country and language is often is a common example as
like car and manufacturer and things like that I cut and but
the 1 reason I mentioned org ID and team ID is because often
the data is then partitioned based on that or the primary key
is some combination of those things.
So they're really often always being queried together.
And Progress's default, which is a really sensible default, is
to assume that different conditions are independent of 1 another.
And in a where condition, that means it's really going to underestimate
how many rows are going to come back, which can lead to bad things
like nested loops being
Nikolay: used.
It just multiplies estimates.
Michael: But it also has the opposite problem in group by if
you group by multiple things and they're actually very related
you're going to get a massive and over estimate instead of under
so it can have both it can have issues in
Nikolay: both directions.
Michael: Yeah so once you know you're like as the maker of that
software you're probably very understanding of which of your
columns are going to be very...
I think the technical language is functionally dependent on others.
Nikolay: How?
Michael: Well, like orgid and teamid, you know that.
Nikolay: I don't like this answer, You know that.
I want the answer which could be written for humans and maybe
not only humans.
Some answer, like some step-by-step how to recipe algorithm or
something, right?
Look here, check this, and then decide.
This is what I think is missing here because as with indexes,
great statistics is something that is not working by default.
It requires an effort from engineer.
And I think, as I told you before recording, I never used it
for real in production to solve any problems, like 0 cases I
have.
It appeared, it was added to Postgres 10, 7 years ago.
During these 7 years, several times I considered it.
And you know, like when something, like we see, okay, the row
estimate is completely off, like thousand times, million times,
completely off.
What to do?
Of course, analyze, analyze, recollect the stats, see if it helps.
If it helps, no, it doesn't help.
What to do?
Then, old school approach, okay, like probably 100 buckets is
not enough.
default_statistics_target is a global setting, but you can control
it on an individual column level, yeah, and you say, okay, I
want 1, 000 buckets here.
And actually, I remember Tomáš Vondra was a guest on Postgres
TV we discussed this and Tomáš's opinion was hundred buckets is
quite a lot so it's not a good idea to always fix it increasing
this number but I see sometimes sometimes
Michael: yeah I think that might also sometimes help because
it not only increases the number of buckets but also increases
the length of the MCV the most common values
Nikolay: but if you don't use if we don't use standard statistics
Michael: with regular yeah with regular statistics target on
a column you also get a bump in the number of MCVs tracked.
Nikolay: Ah, okay, this I understand, yeah, yeah, yeah.
Michael: Which is, I think, might also be sometimes helpful for...
It might sometimes solve the problem.
So I can understand why throwing more sampling at the problem
could be helpful there.
Nikolay: And I'm okay to spend more disk space and memory for
this.
The problem usually if you raise default_statistics_target to
1000, for example, is that analysis takes longer.
Which is acceptable in many cases.
I wanted to mention very big news which occurred last week, like
you know it, you name it.
Michael: Well, the commit Postgres 18 that unfortunately won't
include what we're talking about today, extended statistics,
but it's still huge and really positive news.
Although I've seen that there are some performance issues.
Nikolay: So in Postgres 18 it is expected that during the upgrade
Michael: yes
Nikolay: We all know that analyze is on user shoulders, basically,
on DBA or DBRE shoulders.
pg_upgrade doesn't run analyze.
If it's in place, brief upgrade, very fast upgrade, taking only
a couple of minutes.
It's pg_upgrade --link or -k.
So hard links are used, it's very fast, and it also dumps, restores
the schema, because new version has different features, so this
schema needs to be dumped explicitly and restored.
This is automated.
But then it says, okay, now it's time to run analyze, and there
is analyzer in stages, which I don't like.
We talked about it a few times.
But it's basically, it's outside of the scope of pg_upgrade
to run analyze.
We also, I criticized managed Postgres providers like RDS, Cloud
SQL, Azure because they don't automate it as well and live on
shoulders of DBR.
And people keep forgetting it, and after upgrade, they don't
have statistics at all.
So, big news is in Postgres 18, it was just committed and hopefully
won't be reverted.
Statistics will be dumped and restored.
Michael: Yes, and that's even better than, in my opinion, that's
even better than analyze being automated.
Nikolay: Of course, it's better.
This is the answer I've got when I raised this on Twitter on
X many months ago saying that's not all right that managed service
providers don't do it.
Somebody said like let's wait and it was the right idea So if
now statistics is dumped and restored, we have old statistics
and it's good.
Michael: Yeah, and my understanding is this is a pg_dump feature,
therefore it should work...
Let's say it does end up in 18, it should work for the next upgrade
you do, no matter what version you're coming from.
Nikolay: Yeah, I also thought about it.
It's interesting.
I'm not 100% sure, but of course, definitely you can run, you
can use new pg_dump against some old Postgres server, and hopefully
this feature will work.
It means that we will be able to upgrade old servers to new without
need to run ANALYZE explicitly.
Yeah, and
Michael: it means less downtime.
I consider that time you're running ANALYZE to still be there,
could effectively be down, even if you try to restore connections,
just because queries could be much, much slower than they should
be.
So, yeah, I think for me this could drastically reduce effective
downtime for a lot of projects during upgrades.
Nikolay: Yeah, I agree, because in my opinion, ANALYZE in stages
it's a wrong feature, completely wrong.
And I prefer keeping an ANALYZE inside maintenance window.
And of course, if you have defaults, this is a target elevated
to some high numbers, thousands.
In this case, it takes longer, especially if you have like some
people have a lot of Database objects, a lot.
I just recently had the pleasure to deal a little bit with a
case with 200,000 tables and more than 1 million indexes and
if you decided to raise default_statistics_target there, ANALYZE
will take ages and even if it's parallelized like yeah so keeping
statistics is good.
Michael: Let's back to the topic though, you might still want
to run ANALYZE after a major version upgrade.
There are a couple of caveats, even in the 18 commit, as to things
that won't get pulled across.
And one of them is extended statistics.
So if you are using extended statistics, you will, and they're
important for your query performance, you will still need to
run ANALYZE to actually get back up and running.
Nikolay: I always like running ANALYZE.
For example, you know, if you have partitioned table, the vacuum
doesn't maintain statistics on the parent table.
Somehow if you run ANALYZE explicitly you have your statistics
so I just like running ANALYZE.
Michael: I think there's even an improvement, maybe it was in
17 that you can run ANALYZE only on the parents so you don't even
have to analyze all of the partitions, which is quite cool.
Nikolay: Yeah, so partition statistics on partitioned tables probably
it's another topic and it's yes itself but back to extended statistics.
So my point is since this is a feature which requires you to
run some things explicitly and make decisions, there should be
recipes.
For indexes, we have a recipe.
We have sequential scan, we see some filter or order by, okay,
we know this index should help.
We verify it, ideally on full-size clones.
We see it helps, we deploy it to production.
People who don't have fast and cheap clones, they check it right
on production and see it works.
Okay.
So that's it.
As for statistics, which extended statistics, which was added
as we discussed in Postgres 10, 7 years ago.
During these 7 years, several times I said, okay, Maybe we need
to create statistics here.
Let's try.
I remember 0 cases when this was solution.
We always ended up having different solution like creation of
index or maybe redesigning schema sometimes to the question of
previous discussion with Frank, right?
I mean, redesigning schema, denormalization and so on.
So I don't remember conclusion.
Okay, in this case, CREATE STATISTICS is our solution to the
problem and that's Saves me it Honestly, it builds some Weird
feeling I'm missing something.
Everyone is using something.
I'm not using
Michael: Well, interestingly the reason 1 of the main reasons
I wanted to bring this up is I think extended statistics might
be underused globally I think a lot of people don't know that
it exists.
Nikolay: Because there are no recipes that's this is my point
we need recipes when Very concrete recipes how to decide it's
worth using it right here.
Michael: Yeah, interesting.
But I do have something as a chance.
Nikolay: Give me a schema only dump.
I will give some LLM, good 1, this dump and ask to find just
based on column names, to find some ideas what can have correlation,
then we build statistics and hope it will help someday.
But it's a weak solution.
Michael: I also think it's flawed, because I think if it's not
actually causing query performance issues, then you're paying
that penalty for Analyze being slower.
Well, again...
For no benefit.
And actually, that's a problem.
Nikolay: I didn't finish my thought.
I'm okay to pay some penalty to Analyze more, keep more statistics.
It's not a problem.
Unless we have hundreds of thousands or millions of objects.
In this case, I will be very careful.
But if it's only like a few thousand of objects I mean indexes
and tables it's it takes 1 minute or 3 minutes to run Analyze
on this database it can be many many many terabytes 1 or 3 minutes
I don't care and in terms of storage it's nothing and memory
it's not a lot maybe I'm wrong but I think so.
Michael: I think you're right and I think that's important for
people to realize that it's not dependent on your data size because
we're sampling at this point like because it's a sampling thing
it doesn't scale linearly with how much data you have.
Nikolay: Also important disclaimer, I have set of mind targeted
startups heavily because I'm building my fourth startup and our
clients, consulting clients and clients for products mostly are
startups and that means rapid development, rapid, really rapid,
sometimes a couple of deployments per day or more.
It means that maybe we, statistics we decided to keep, maybe
it's not needed today, but maybe it will be needed tomorrow because
we make some new features and so on right and it's maybe it's
better to have it because we know there is correlation in between
these columns so let's have extended statistics over all these
columns but we lack recipes this is the point So like like recipes
leads to under use of this feature.
I
Michael: Think they might also I think historically you've also
mentioned having a bias towards OLTP systems in production and
I think there's also a chance that because often you're hyper-optimizing
queries and that's a good thing going for index only scans and
you know very very limited data retrieval very small number of
rows a very very precise lookups you're probably not hitting
these bad plans in a lot of those cases that you're skipping
straight to a really good
Nikolay: that's interesting for
Michael: that's really hard for the planners to get wrong because
you're giving it the ideal index to use for the important access
patterns and it's possible that this comes up a little bit more
when people are having to serve more user-defined queries, maybe
analytical ones that have any combination of parameters.
Nikolay: Right, like OLAP queries and so
Michael: on.
Yeah, exactly and I think in those cases maybe sometimes just
not knowing what's going to come you can't create indexes for
every case you do and then this is slow
Nikolay: down writes unlike statistics so this is a super big
difference.
Michael: Yes so I think there's a chance that bias towards OLTP
and also being able to often add the indexes you need for a smaller
number of access patterns or that like a lot of queries per second
might be that this just isn't as important in those cases.
But in terms of recipes, I think the big 1 is bad row estimates
on important and slow queries.
So if a Query is important and a lot slower than you need it
to be, and there's a really bad row estimate in an important
part of the plan.
So like, not in a part of the plan that is fast, but in a part
of the plan that is slow.
The really famous case is a nested loop that thinks that the
planner estimates it's gonna return very, very few rows per loop,
so maybe like 0 or 1.
So therefore, it's thinking, well, I only need to do this a relatively
small number of times, therefore, it's quickest and easiest to
get started with a nested loop and I'll be done really quickly.
But in reality we'll create maybe a thousand rows or more per
iteration.
Actually in those cases a hash join or merge join would have
been it would so much faster.
So those cases I think can benefit.
But even actually there's a blog post I was thinking of that
blogged about a time where extended statistics helped them a
lot I put the plan through our tool and noticed that yeah a bad
row estimate was as highly scored as it could be, but so was
an index efficiency issue.
And I think, looking back, they could have added a better index
and solved their performance issue just as well.
So they've solved it with great statistics, but they could have
solved it with better index and probably the query plan they
got in the end could still be improved further by a better index.
Nikolay: So I have an idea for myself and who is listening is
welcome to join.
Next month I will have a challenge for myself.
So every time I deal with some query optimization, I will put
consideration of extended statistics at first places.
I will think, can it be helpful here?
And so on.
Just prioritize it and think about it more.
And after 1 month of this, 30 days, or month, February is shorter
month, right?
So 30 days.
After it, I will make my conclusion, should I keep this as 1
of primary tools for query optimization or it's just secondary
tool?
How does it sound?
Michael: I would love to hear that, and from anybody else that
does try it.
1 thing I'd encourage doing is also benchmarking the performance
hit on analyze of that tape.
Like if you do add it to a table, how long does analyze take
before and how long does analyze take after and I think it would
depend on which there's like a few types of extended statistics
that you can add and you can add all of them for
Nikolay: expressions or you mean
Michael: they could it there's like 3 There's like 3 parameters
you can give it.
So, nDistinct, dependencies, and mcv.
And I think mcv will be by far the most expensive in terms of
analyzed performance.
Oh, by the way, another thing that the docs mentioned can be
penalized is query planning time and maybe query planning buffers
for that matter.
Nikolay: If you have a lot of extended statistics created by
us, a planner can take longer?
Michael: Yeah, well it makes sense, right?
It's considering more information up front.
Nikolay: Right, makes sense, yeah.
Michael: I don't know how, but I'd be interested in the benchmarks
of how much longer, how much more, like.
Nikolay: Yeah, but in this case 2 things.
First is, well, both Benchmarks sound to me like single session
benchmarks.
So it's not like we run a lot of like, we can take 1 session
and check just speed maybe multiple times for good statistics,
right?
So First thing sounds to me like checking analyze speed.
And I think we need to remember that if we talk about a single
table, it's OK.
I mean, no problem.
But if we talk about many tables, it's worth remembering that
it can be parallelized.
And its recommendation is to use vacuumdb with option hyphen,
hyphen analyze and specifying number of jobs, number of processes,
backends basically, right?
Which will execute this.
And if we are alone on the server, we can take, for example,
the number of vCPUs we have on the virtual machine, for example.
This is 1 thing.
But if it's a single table, it cannot be parallelized.
It will be a single thread at any rate.
Michael: No.
And there's actually 1 more thing that I think worth trying just
before we wrap up is if it is a huge penalty but looks valuable
to query performance, which I'm not sure you're going to come
across, but we'll see.
I've wondered about reducing statistics target for those columns
so you could reduce it and then add multi-column.
It just is an interesting idea of getting back some of that analyzed
time.
Nikolay: I know a very good SQL optimization expert who recommended
reducing default_statistics_target, and I'm going to communicate
with him very soon.
Yeah, great.
But this battle in my mind was clear like 10 years ago already.
Like reducing default_statistics_target or per column parameter
was considered not good idea in my mind, compared to increasing
it somehow.
I don't know, like this is from various cases, But I'm going
to use this as well as a part of my challenge just to think about
this as well, because I will talk to him soon.
Michael: So.
I've got an idea for you.
Nikolay: Let me comment on the second.
I already start forgetting what you said about...
What was the second 1?
First is less benchmark, you said benchmark.
I don't...
I'm very careful.
Analyze?
Michael: Yeah.
And also query planning time or query planning buffers.
Nikolay: Query Planning time, this is good.
Yeah, buffers as well, that's great.
So increasing default_statistics_target globally definitely should
affect this as well, right?
Globally,
Michael: yeah.
Nikolay: Here, if somebody is going to do it.
Don't make a mistake my team and I did a few months ago when
we published an article.
So when you just connect to Postgres and you do something, planning
time can be much bigger because of lack of rel cache and it's
loaded, right?
Second time you run this, it's very different.
So planning time, first planning time and second planning time,
they are very different.
If you have connection pooler, most people do.
It means that you don't, like it's not a good idea to make conclusions
from observations of the first round Yeah, yeah, so so
Michael: right point.
Nikolay: Yeah, so if even if statistics Extended statistics affects
planning time.
I think should be so Question is is it only for the first round
or the second round as well?
Who knows, right?
Because if only first round it means like rel cache all...
Well, it's not rel cache here, right?
Well, it's an interesting point, just to check.
Michael: Yeah, based on the notes in the documentation, I got
the impression it would be for every
Nikolay: planet query.
Because it's statistics, it's not real cache.
So anyway, I would check multiple times.
Michael: 1 more idea for you, especially talking to that person
you mentioned I wonder if you could come up with a recipe for
which columns to reduce it on I reckon there's a whole bunch
of really boring columns like unique IDs you know primary key
columns type thing Do we really need high statistics targets
on those or could we get away with much much lower?
Do you see what I mean like ones that we know are unique in it
if we're keeping statistical on the most common values?
Nikolay: Well, I agree with you, but what's the point to optimize
in this area?
What what are our savings?
Michael: In my reducing the time analyze takes
Nikolay: As I said, I don't I don't really I Don't really bother
by a longer time.
Okay, it's 3 minutes against 1 So what
Michael: That is quite a big deal, isn't it, in terms of downtime?
Nikolay: I don't know.
We have a recipe for 0 downtime.
We can afford a couple of additional minutes being spent, and
nobody notices it, because It's a target cluster running on logical
replication.
We can do very long analyze there.
So even 1 hour, nobody notices.
It doesn't affect downtime at all.
So I don't know.
I don't know.
Like I can imagine this will save something.
We can find many places like that, right?
For example, in some cases we can start tuning autovacuum at
table level.
And so many times I saw teams, various teams, went this path.
And then we pulled them back, saying, let's stick to defaults.
Because table level settings often cause more harm than good
Because they add complexity, a lot of complexity It's hard to
maintain.
Then you go microservice, you have a lot of clusters and this
table level Well, it's so hard to maintain.
So and you talk about column level settings.
Michael: All I meant is, if you can find a really simple rule,
yeah exactly, if it's a unique column, reduce the statistics
target to 1.
Nikolay: This is good if it goes to hackers and the core itself, and implementation
which doesn't require effort from user.
This I agree with.
But if it's...
Again, My main concern, my main comment about extended statistics
is it requires effort, it's not clear, there are no clear recipes
how to apply it, so it ends up not being actively used unfortunately.
Maybe in the next 30 days we will have some recipes, right?
Michael: Sounds... I'm looking forward to it.
Nikolay: Sounds good.
Okay.
Thank you so much.
Michael: Thanks so much Nikolay.