Nikolay: Hello, hello, this is Postgres.FM.
As usual, I don't remember episode number.
Actually, I don't care.
I know 100 something.
And my name is Nikolay, founder of Postgres.AI.
And as usual, my co-host is Michael, pgMustard.
Hi, Michael.
Michael: Hello, Nikolay.
How are you doing?
Nikolay: I'm doing great.
How are you?
Michael: Yeah, good as well.
Nikolay: I actually feel very good after therapy session we had
last week.
Michael: Glad to hear it.
Nikolay: Yeah, So I know it's your choice.
I'm glad you chose a very practical topic.
Technical, practical.
So yeah, what is it?
Michael: Yeah, I chose index-only scans.
I was aiming for technical and practical, but I also wanted something
that's still beginner-friendly.
Like, I think there's enough depth we can go into with index-only
scans.
I'm hoping there's something in here for everybody.
But I definitely see some beginners not fully understand index-only
scans, how to get them, or even knowing that they exist, really
truly understanding their benefits, and also the downsides when
they go wrong, or when they aren't so efficient.
So yeah, lots to cover I think, but we can start nice and gently.
Nikolay: Yeah, and since this topic is related to query optimization,
it's your field, I would say, because of pgMustard, so I will
let you to be in the driver's seat.
Michael: Yeah, well, I feel like you are as much, probably more
of an expert in this field than me still.
But yes, it's definitely something I've had to look into a lot
for the product I work on.
And we've got a couple of tips around this within the product.
But I actually wanted to start like, super basic, like,
index-only scans are a type of scans.
I think it's worth covering that we've, we've got other scan
types and Postgres gets to choose between them in certain situations.
We can cover when it gets the choice of doing an index-only
scan, but I wanted to start with even the absolute basics.
If, for example, we don't have any indexes, the only scan option
we have for a table is a sequential scan.
Yep.
Going through the pages in order, one at a time, and then if we
have an index...
Nikolay: Sequential or parallel sequential, if we distinguish between
the 2.
Michael: You consider that a different type?
Yeah, interesting.
Nikolay: Well, well, technically it's different.
The planner has to make a choice.
Michael: True, true.
Oh, actually, this came up, and maybe this is too off-topic,
but do you think parallelism should be on by default, like with
Postgres?
Like, maximizing
the work together?
Nikolay: I think JIT should be off by default.
Michael: This is when it came up.
Yeah.
Nikolay: I got beaten very badly again and it was in my home.
I had a bad, bad, bad, bad, bad situation.
I don't understand why JIT is on.
I completely don't understand.
It just hurts workloads and that's it.
So yeah, I think random_page_cost should be closer to seq_page_cost.
We will discuss this, right?
random_page_cost adjustment, and we discussed it a few times.
And as for parallelism, I saw Jeremy Schneider's opinion that
maybe in some cases it's better to switch it off to have more
predictable behavior of query processing.
Because if you...
Well, there are many things like that.
If you have parallelism turned on, which is on by default, 2
additional workers usually.
But there are several such things and definitely it's our topic
today.
Index-only scans is an unpredictable thing and heap-only tuple updates
is an unpredictable thing.
Quite recently I saw talking with customers, I saw opinion, which
is probably a reasonable opinion from backend engineers,
or like, oh, this is something quite unpredictable.
We have many, many, many installations, various situations, different
Postgres infrastructures.
So if we think we cannot, if it was only 1 cluster, we should
think, oh, this is like our pet.
We will take care of behavior closely, watch it closely.
But if you have thousands of setups, you cannot rely on such
things.
And maybe you will prefer, like let's raise this question
and in the end maybe we'll try to answer ourselves.
So to have predictable behavior, maybe you should switch off
parallelism, stop relying on heap-only tuple updates, HOT updates,
and also stop relying on index-only scans because they can degrade
to regular index scans, right?
Michael: Yeah, or even slightly worse, I think.
So yeah, let's go back to basics.
Nikolay: Basics also, question.
Do you feel the name is kind of off?
If we had name choices made right now, maybe index-only scans
would be named as index scans, and index scans would be named
like, I don't know, like index heap scans or something, because
it's hard to...
You need to explain it, And if people don't work with Postgres
planner every day, they forget and they expect index scan is
what deals with only indexes, but then index-only scan, what
is it?
Michael: Yeah, that's a really good transition into what these
are.
Yeah, so index scans came first, and I think that's probably
like why we're stuck with that name.
And in Postgres... in a lot of other database management systems,
not including Postgres, you have indexed organized tables.
But in Postgres we don't have that.
We have heap and indexes are what is often called secondary.
So to look something up via an index, we'll check first in the
index and then that will point us to the right place in the heap
to find that row.
Nikolay: Probably right.
Michael: Yeah, yes.
Nikolay: At least pages are right, but offset might be wrong, right?
Michael: Yeah, well, back to Heap again.
Let's keep it simple to start with.
Nikolay: For simple, yes, for index points, but it doesn't know
if this tuple is alive or dead.
This is why we need to check heap.
Michael: Or even, I guess not even like alive or dead, but visible,
like it might be alive to new transactions.
Nikolay: Right, visible to current transactions.
Michael: Visible, exactly.
Cool.
So that becomes important in a moment when, in fact, until, you
know, when index-only scans were added to Postgres, I found out
it was...
Nikolay: It was not very long.
Michael: 9.2.
Yeah.
So yeah, not that long, like in the history
of Postgres.
Nikolay: I remember living without them very long, of course.
And this mantra, like, indexes don't store visibility information,
I first heard 20 years ago, and it was a rule.
Index scan must consult Heap to understand visibility.
Michael: Yes.
So, and looking back, in fact, the Wiki includes, the Postgres
Wiki includes a really good entry on index-only scans and explains
that the majority of the work to add index-only scans was work
on the visibility map to make it crash safe.
So that, I found that fascinating looking into it for this episode.
I didn't know that until yesterday.
So yeah, super fascinating.
So the index-only scan, probably should actually explain what
it is and does.
If the data that we need for the query is already in the index,
so very typical for a B-tree index, not for all index types,
but in a B-tree index, we have the data that we're indexing in
the index.
If that's all we need for the query we're running...
Nikolay: If we don't use SELECT *, if we
only select, for example, columns which are in the index, this
is conditional, right?
Michael: Yes, exactly.
So if the columns we're selecting are in the index, or for example,
if we're doing like an aggregation, like COUNT *, we're not
really selecting a column, but we... yeah.
So yeah, there are like a couple of cases.
Yes, exactly.
And yeah.
And if the index type supports it, like B-trees do, then we can
get an index-only scan.
So yeah, really cool feature.
Nikolay: This is like, okay, we deal only with values which are
present in index keys.
Index keys maybe can be called.
Maybe not, maybe it's a bad idea to call that.
But yeah, the planner might choose, may choose or may not choose
index-only scan.
It still may think index scan is better because the second component,
like I agree with everything you said so far, but then you say
we don't consult the heap.
Well, in general case, index-only scan consults the heap because
we don't have visibility information still, right?
But in some cases, it skips it.
Michael: Do you count the visibility map as the
heap?
Nikolay: Yes.
No, no, no, no.
No, I mean, we check the visibility map and see what bit it has in
terms of all visible bit for the page and the heap we need.
If that page is marked as all visible, this is a win.
But it's not guaranteed.
It may not be marked as all visible.
In this case, it's kind of degradation to the behavior of an index scan
for this particular value reference to the tuple.
And we need to go to the heap and first see if this tuple is visible
to our session, and secondly, probably we will jump inside page
to proper position because of Heap-Only Tuple chain, right?
But yeah, that's it.
So I mean, everything is right, but it's not guaranteed that
we won't consult heap.
Michael: Yeah, good.
Yes, exactly.
Good point.
So heap, you'll see this in EXPLAIN plans as heap fetches, if
you're running EXPLAIN ANALYZE.
Nikolay: Even if BUFFERS is not used in EXPLAIN (ANALYZE, BUFFERS).
Michael: Yes.
Nikolay: Even if you skip BUFFERS, this kind of buffers you still get.
Michael: In fact, imagine if you didn't have heap fetches and
you only had buffers, you'd have to be running it twice to spot
these, like, once.
Anyway, so, it's I really like that they call this out explicitly.
It's really helpful.
And as you mentioned, like, let's say we're scanning many, many
rows for our index-only scan.
So, we're doing a range scan across maybe returning 100 rows
or something.
And we only have to do 1 or 2 heap fetches.
That can still be a huge win.
It's only if the proportion grows to a decent.
So, yeah, 2 big benefits.
Let's go back to why even bother with all that work to make the
visibility map crash safe?
Why even go to the trouble of doing this?
It's not only the benefit of not having to do those reads on
the heap, but also I think data locality.
Like if we're doing a range scan, the hundreds values on the
index are likely in a much smaller range of index pages than
they would be heap pages.
I mean, if they were all inserted in order and there have been
very few updates, maybe they are, of course, only a relatively
small number of pages, but they could be anywhere.
It could be really random reads.
So I think historically, especially if you consider when this
was added, doing those random reads on the heap could be quite
a lot more expensive than doing the reads.
Nikolay: Right, and if there is no data locality, each tuple
sits in its own page, basically, buffer, and we have a lot of
buffer operations additionally, just to check visibility, basically,
in this case.
If we have all values already in index, there's a lot of buffer
operations just to check visibility.
It feels super inefficient and index-only scans is just an optimization
of index scans.
But they'd be super good if visibility...
Like If you have, for example, if you just loaded your data into
a table, run a vacuum once on the table, and don't do any writes
anymore, this is a perfect situation.
Heap fetches will be 0, the best case for index-only scans.
But in the worst situation, you'll have the maximum number of
heap fetches, and this should be maybe the same number of buffer
operations as index scans.
Michael: It, plus, you've had to check the visibility map,
which admittedly is not many reads, like a visibility map is
tiny, but you don't have to check that if you're doing an index
scan.
Like
it's...
Nikolay: Oh, so you think index-only scan in this extreme case is worse
than index scan?
Michael: Tiny bit.
Nikolay: I know planner might decide, I think based on the state
of visibility map, in some cases it might decide to choose index
scan, even the main condition is met.
Michael: I don't know that it does.
Like in theory, that would make sense.
I don't know for sure that it does.
Nikolay: It's worth checking.
It's a simple experiment.
Worth checking and looking at buffers.
Yeah.
But for simplicity, number 1 thing to memorize, index-only scan.
If you have autovacuum tuned, index-only scan is the best what
you can get from Postgres, for example, for aggregate
SELECT COUNT, things like that.
If you get index-only scan, even if it's like a lot, a lot of
rows, this is the best.
Michael: Well, I completely agree.
Nikolay: Index-only scan and heap fetches are 0.
This is the ideal situation.
Michael: And 1 more condition, Rows removed by filter 0 as well.
So I see sometimes people get excited because they see an index
scanned or an index-only scan and then they stop because they
think I've got an index-only scan, but it's returning 100 rows
for every … So
Nikolay: you get 1 row, right?
Michael: Yeah, exactly.
So there is this 1 other gotcha that sometimes catches beginners
out.
You would never be caught out by that.
But some people see index-only scan and think, great, I'm done.
And actually, it's still hideously inefficient.
So yeah, they're the 2 things we look out for.
Nikolay: Okay.
So yes, in phrasing this, you really need those rows that index-only
scan brought to you, right?
You really need them.
Because if you see 99% was filtered out, it means those rows
were not needed in the first place, and you just need to find
better index, for example.
Michael: Exactly.
Nikolay: Also, let's touch this thing.
You create an index, you run vacuum, no writes yet.
You run SELECT COUNT(*) from this table, or SELECT column,
indexed column, like ID, for example, which is technically the
same because it cannot be null.
We know count, won't count, rows, which are null.
And * cannot be null, even if...
It's complicated.
Maybe we should save it for another episode about counts, right?
But you run it, but you still see sequential scan in the plan.
This is happening all the time, right?
Michael: That's a
good point.
Yeah, good point.
Or parallel sequential scan.
Nikolay: Or parallel sequential scan.
And you think, why?
Why, like, Index is better, right?
Because index-only scan is better.
I know it.
It's much fewer buffer hits or reads, or both.
Right?
What to do?
Michael: Yeah, so before we jump straight to it, you can use,
if you want to ask this kind of question yourself, there's like
some parameters that we can set locally to help us diagnose these
things.
So we could use enable_seqscan and set that to false briefly.
And then hopefully that will encourage an index-only scan.
We can compare the costs.
And once you compare the costs, it should become clear.
Nikolay: How to compare the cost?
Disable scan and look?
Michael: Run, EXPLAIN, ANALYZE, SELECT counts, or maybe with
BUFFERS.
But you don't need it.
In fact, you don't even need ANALYZE.
Nikolay: Yeah, yeah, just because we want to compare planning
cost.
And if sequential scan was chosen, it means the cost was lower
than for index-only scan.
Michael: Yes, exactly.
And then I'm guessing where you're going with this is probably
random_page_cost.
Nikolay: Right.
This happens if you have default random_page_cost.
And it doesn't make sense if you have fast disks, random accesses
close to sequential access on SSD and NVMe, right?
In memory, it's also so.
You might have very slow magnetic disks, but the database is
well cached and you rely on it somehow, right?
It also can happen still.
In this case, random_page_cost is 4 compared to seq_page_cost
is 1, and to work in a B-tree it's random access compared to page
by page reading of heap sequentially.
So index scan, index-only scan, the planner looks at
random_page_cost.
If it's 4 times more expensive, it might prefer seq_page_cost.
Michael: Especially if it can do it in parallel.
Yeah.
Yes, yes.
Nikolay: Well, index scan and index only scan also can be parallel.
Michael: True.
Good point.
Nikolay: Like we have duplicated.
Michael: So maybe that doesn't matter.
Yeah.
Good point.
Nikolay: Bitmap scan, index scan, index-only scan, seq scan,
and then parallelized versions of them, right?
Michael: Yeah, I skipped bitmap scan to keep things simpler.
But we
maybe should do a whole episode on that because it is
interesting enough.
Nikolay: Yeah.
Yeah.
And for index scan parallelization cannot be, as I remember,
it cannot be applied to navigating inside B-tree, inside the
tree, going from root to leaf.
But it can be applied to fetching leaf pages in tree, and also
accessing heap, and also traversing leaf nodes.
So it has a bidirectional list for all the leaf nodes, allowing
not going up and down all the time, not starting from root for
each entry, right?
We just need the first entry.
And then I think it can be parallelized, dealing with leaf nodes.
This is what I remember, I might be slightly wrong about internals
here.
But again, index-only scan is the best if you need a lot of rows.
For SELECT COUNT(*) you might still see a sequential scan.
It's a good sign you have not tuned random_page_cost, which we
recommend to everyone to do sooner because later it will feel
more risky.
Michael: Scarier, yeah.
Nikolay: Yeah, scarier because you have a heavy workload and changing
this might flip your plans in some unpredictable manner.
So yeah, it's a big task, But if you do it early, live with it,
it feels more reasonable than keeping defaults.
Did you have cases when you see this, okay, cost for sequential
scan is lower, that's why it's chosen, But when you say enable
seq scan to off, giving some huge penalty to sequential operations
in planner's mind, then you see timing and buffer operations
so much better for index-only scan.
You think, oh wow, planner is so wrong here.
You had it, yeah, I have it all the time.
And this is a good sign we need to change random_page_cost by
default in all databases, basically.
Michael: Yeah, I think we've discussed this in previous episodes,
but in the past I've seen, like, conservatively, people go down
to 2, but
more aggressively.
Nikolay: And you know the reason, right?
I mean, ah, not 2, I was thinking, why default is still
4?
You know, like why is it still 4, the default?
Michael: Yeah, I don't know.
I've seen argument, or I've seen people mention that they don't
want, like people have old systems and they don't want to change
those.
But yeah, I agree with you, but that is the argument I've heard.
Nikolay: Defaults are applied to new systems. New systems are on SSD...
Michael: I understand. But that's what people say.
And I actually think also, even if they didn't, like, It's not
about old systems versus new systems, it's about SSDs versus
spinning disks.
It's about disk type, mostly.
Most people aren't setting up new systems on spinning disks.
Like most people are setting up.
In that case, why are we forcing the majority to have to change
their default instead of like default should be for the majority?
Yeah.
Nikolay: it's second place after JIT, or maybe default shared
buffers.
Third place.
Third place goes t random_page_costs.
Let's have maybe some chart of defaults we don't like, we would
like to be changed.
By the way, things are changing slowly.
Yesterday I learned that pg_dump is not a backup tool anymore.
It's off topic, but it's very similar.
So many years of discussing, it's like you're swimming against
the current all the time.
You say something, but you open documentation and it says this,
and you know every experienced guy says this, but still...
So defaults are similar.
So Postgres until version 18, in documentation stated, pg_dump
is a backup tool.
Like, first sentence about pg_dump.
And finally, like 5 days ago, Peter Eisentraut committed the
change.
pg_dump is a tool to export PostgreSQL databases.
Michael: Yay!
Nikolay: Not backup.
But this will change only in a year, in PostgreSQL 18.
But it's already a win, like so good.
I hope defaults also, like they are slowly changing.
Log checkpoint was on, work_mem.
Yeah.
Is
it?
Michael: hash_mem_multiplier has been, like work_mem's been increasing very
slowly over the years.
hash_mem_multiplier doubled in like not that long ago.
Like there was...
Nikolay: Autovacuum default throttling 10 times.
But it was many years already ago.
Yeah, slowly it's changing, it's good.
So random_page_cost, most people say, decrease it.
1.1 maybe.
You said some guys decide to go with 2.
Well, I can say some guys decided to put 1 there, but put 4 to
seq_page_cost.
Michael: What?
Nikolay: Yes.
Michael: Okay.
So I've seen sensible people that have done testing, I've seen
set anywhere between 1.1 and 2.
And I tended to go closer to 1.1 if the performance results.
Yeah, I
think that's really cool.
Nikolay: Crunchy, after we did the episode about something, They
tested it and published some details.
Maybe we'll find them a touch.
Their benchmarks showed that 1.1 in their case, I think it's
AWS and GCP or something, right?
It's still In their case, they found out that 1.1 is slightly
better than 1.0, according to some benchmarks.
Not mine, I don't know.
So since then, I also kind of put 1.1 before I tried to put
1.0.
So I don't know.
1.1 is a good start, right?
Enough maybe here.
Michael: Let's go through a few other recommendations.
I think this is 1 recommendation of something that you can do
to encourage index-only scans or recommendations that are around
index-only scans.
We did a whole episode, for example, on over-indexing, and I
think that's relevant here.
Nikolay: Before we go to over-indexing, 1 second.
It's super important to tune to autovacuum because otherwise heap
fetches will be super high and you need to, you need to, autovacuum...
autovacuum is not only about vacuuming or collection of statistics
or fighting with transaction and MultiXact ID wraparound.
It's also important because it maintains visibility maps.
Michael: Yes.
And it's the only thing that maintains visibility, like the only
thing that can set those bits
Nikolay: Directly, yes, yes, directly.
Michael: I think this is important because any, let's say we've
got a page in Postgres that has 20 tuples on it.
Like really common to have like a couple of dozen of tuples on
a page.
If any of those rows are changed, the visibility of that row,
like the bit gets-
Nikolay: It's out of the game.
Yeah, this page-
Michael: Yeah, it goes from 1 to 0, right?
It goes from all visible to not all visible, or not guaranteed
to be all visible.
Nikolay: No, it's not all visible, that's it.
We don't know, but it already cannot be considered all visible,
that's it.
Yeah, technically, these tuples might be visible to everyone.
Autovacuum just didn't mark it.
Michael: Didn't get there yet.
Nikolay: Yeah.
So autovacuum needs to do this as well.
That's why frequent visits of tables are so important.
Not only because of deleting that tuples.
Michael: Yes.
Now is, I guess it is possible that you will never benefit from
index-only scans if you have a table, but maybe like a small table
that's constant, that the rows are constantly being updated.
But if you, most of us have tables where, or at least like the
cases we're thinking about here, you have like some data that's
pretty old that doesn't change often that we do still want to
read.
And like in those cases, as long as you can, yeah, tuning
autovacuum super valuable for maintaining the performance of index-only
scans.
Is there anything else around that?
I actually shared just recently in my newsletter an old post
by Tomas Vondra.
I thought I revisited it.
He's updated it for the latest versions of Postgres.
I'll share it again because I think it's so good
Nikolay: Oh yeah, yeah, I also saw it. That's great.
I actually expected this because after they acquired the second
quadrant, they broke that block and some links didn't work.
And I used archive.org to fetch some old pages.
It's great.
These posts needed to be updated.
I also think they should be part of documentation, honestly.
Michael: Nice.
Well, high praise.
Nikolay: Yeah.
So, but documentation tends to avoid good how-tos and so far,
only basics.
There are how-to notes in documentation, but they are scattered
among general documentation, reference style, and so on.
I wish documentation grew like a huge how-to section, but it's
a different story.
But let's not lose this important point in this context.
So autovacuum maintains visibility map, which has this all visible.
It also has all frozen, but that's a different story.
All visible, 2 bits for each page.
And if we have a lot of writes, like many writes are happening,
many pages are having 0 for all visible bit.
And what's important here to understand that partitioning is
so good.
Like if you just consider unpartitioned one terabyte table and
partitioned one where old partitions, like most of writes go to
the latest or few latest partitions and old partitions are like
they can be, they can receive writes but it happens rarely.
Yeah.
And vacuum aggressively fairly frequently visits such pages.
Oh, okay, some write happened in old partition but I immediately
mark this page all visible, and it's only 1 page in this partition.
That's why, like, archive, archive data locality and archive
kind partitions, old ones and only new or the latest receives
all inserts, some updates, they're usually for fresh data.
It means this, like old partitions most of the time will be all
visible for all their pages.
This is so good if massive reads are needed for all data.
Without partitioning, any page can suddenly receive a write,
even if all rows in this page are 5 years old.
And then suddenly, oh, there is some place here.
Let's insert a new tuple.
And it’s out of...
So everything is shuffled, right?
New data and old data, they can live in the same page.
And for visibility map, it's kind of a lot of work for autovacuum,
and visibility map is most of the time kind of in bad shape.
So what I'm trying to say, partitioning is needed not only because
indexes become smaller.
This reason is, over years I realized, is one of the smallest reasons
for me.
Because B-tree height grows very slow, you know.
But this particular thing, this may be number one reason, since
recently I realized it.
Michael: Wow,
you think so?
Nikolay: For partitioning, like, we can say, okay, This partition
is barely touched in terms of writes.
We just read it because people want to see archived data.
But reading is well organized most of the time.
If it counts or something, It's index-only scans with heap fetches
very close to zero.
Without partitioning, impossible.
I mean, and less predictable also.
Michael: Yeah, I mean, in the case you're talking about where
it's append mostly and it's to the right of the...
I've definitely seen cases where you don't touch old data anyway,
whether it's partitioned or not.
But I definitely see this as yet another benefit of partitioning.
I'm surprised you said it's like, you see it as the biggest,
but that's really cool.
Nikolay: Well, actually, I think if we, for example, decide to
create additional indexes to support index-only scans, and we
will talk about methods right now, right?
But in the index-write amplification, I know we have a plan.
But if you, for example, decide... usually we keep the same sets
of indexes on all partitions, right?
But technically, you can create an index for a particular partition.
And if, for example, you created it for archive-style partitions
to support index-only scan, to prioritize them for reads, and
you maybe don't care about write amplification, because again,
these partitions barely receive writes, and HOT updates also
we don't care.
We care about...
So we might decide to create more indexes for all partitions
to benefit from...
Michael: Yeah,
I've not seen that but it's a cool idea.
Nikolay: It's just like, I don't know, maybe it's a crazy idea,
maybe we'll have some walls hit if we implement it.
But it seems reasonable, and for fresh partitions, I never did
it.
For fresh partitions, you avoid creating these indexes because
you want HOT updates occurring.
So, right?
Yeah.
Let's maybe dive into details a little bit.
Michael: I yeah, well, we could point people towards the we did
a whole episode on over indexing and we did another 1 on HOT
updates.
So let's point people at those because I think there's a couple
more like definitely want to get your thoughts on the difference
between multi-column index and include like when you use each
or...
Nikolay: when it was released again like 11? 12? 10? 11? I don't
remember.
Some many years, already many years ago, and I was
thinking, is it only for unique indexes?
Honestly, I remember I had Anastasia Lubennikova presented on
Postgres TV details, some details.
Yeah, or maybe it was an interview because she participated in
this include keyword covering indexes.
So my understanding, we only need...
It might be a size difference also, right?
Because if we want to put in a new column to have multi-column
index, 1 more column we just included, it will participate in
structure, right?
But if we say include, it's just additionally stored in leaf
nodes, right?
Michael: Leaf page, exactly.
Nikolay: Yeah.
And it means that kind of for to achieve index-only scans, both
cases work, but the first case can be also used since it's in
structure, it can be used to verify uniqueness.
Or maybe also constrain exclusion.
Exclusion constraints, right?
Maybe.
For range types.
It's a different story, but definitely for uniqueness.
I now realize that actually we cannot put some data type, columns
of some data type to multi-column index, but we can put them
to include.
This is also a benefit of include.
So unique constraint can, like, we don't want to put index because
it will break the logic of uniqueness, right?
But we can put additional column to include and have index-only
scan, not changing logic of unique constraint.
But also to include, we can put data types which are not B-tree
friendly, which are not supported by B-tree.
Michael: Like not...
Nikolay: Arrays, for example, right?
Michael: Yeah, yeah, yeah.
Nikolay: Polygons or something, like points.
Michael: Although you do need to be careful with size, right?
Nikolay: Of course, yes.
Of course.
Size matters.
But imagine we have like point, 2 numbers, right?
So we can put it to include and the index-only scans will happen.
Michael: I've got 1 more thing that I wanted to make sure we
mentioned, which was something practical and very beginner-friendly.
In fact, probably the main thing that I see stopping index-only
scans in the real world, which is people selecting more data
than they need.
And I know it's kind of obvious, but it's really common in ORMs
to have SELECT * be the default, which is almost always going
to be giving you more columns than you actually need for that
specific query.
But I see people doing it, like, people deleting features, for
example.
Like, changing how a page is structured in an application.
They no longer need all the data from the queries returning,
but don't change the query.
There are so many ways it can creep into people's code that they
no longer need all the data they're returning.
So being careful on that front and also just remembering that
there are these optimizations if you can be, even if the application
does currently use all of the columns, do we need to like what
value is that adding at the moment?
Can you make things much faster by not including that information
at that point.
Nikolay: Right.
Yes.
And finally, maybe let's mention also HOT updates.
I already kind of mentioned that indirectly, but directly, like
if you have, if you need to put extra, 1 more column to the index,
either to...
Doesn't matter if it's to extend this multi-column list or to
include, to have coverage for your selects.
You can lose the HOTness of some updates, right?
Because even if it's include, still, if this column is updated
frequently, these updates cannot be HOT.
They will be much slower.
They will be regular updates.
So it's better sometimes to avoid this.
So this trade-off here, you want select only scans but you lose
HOT updates.
What's needed?
Selects or writes, I mean updates.
Michael: Yeah, I know this is very off topic, but do you know
heap-only tuple updates are no longer heap-only in some cases?
Like in BRIN, there's some changes to BRIN indexes.
Have you seen this?
And now, Yeah, now it updates the BRIN index.
I thought that was funny.
Nikolay: Yeah, yeah.
Okay, so I think we've covered enough, right?
Michael: Yeah, 100%.
Nikolay: For iOS.
When I hear iOS, I think index-only scans, right?
Michael: That’s so funny.
Are you on Android or iOS?
Nikolay: I'm on iOS.
My phone, I mean.
Michael: Index-only scans.
Cool, cool.
Nikolay: Yeah.
Yeah.
OK.
Good.
See you next week.
Bye.
Michael: See you next week.
Bye.