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.
