![LIMIT vs Performance](https://img.transistor.fm/XoJKBsIida2PbzEhDlUv56LV_igZ6S4xTONrCcJ6P7A/rs:fill:800:800:1/q:60/aHR0cHM6Ly9pbWct/dXBsb2FkLXByb2R1/Y3Rpb24udHJhbnNp/c3Rvci5mbS9zaG93/LzMyMTQ3LzE3MTA3/OTEzODMtYXJ0d29y/ay5qcGc.webp)
LIMIT vs Performance
Michael: Hello and welcome to Postgres
FM, a weekly show about
all things PostgreSQL.
I am Michael, founder of pgMustard.
This is my co-host Nikolay, founder
of Postgres.AI.
Hey Nikolay, what are we talking
about today?
Nikolay: Hi Michael.
It's your proposal actually, Limit,
right?
But before that, let's mention
a little bit what our companies
are doing because I think last
time we did it 90 episodes ago.
Michael: Right, sure.
Nikolay: Yeah, so pgMustard, by
the way, I wanted to tell you
yesterday but forgot.
During some session, people asked
me about pgMustard suddenly
and asked, can I recommend?
Michael: What did you say?
I
Nikolay: said, yes, of course.
And became shy.
I should recommend it before they
ask, right?
Michael: It's good that they ask.
Nikolay: So yeah, We discussed
some plan and they wanted...
Obviously we discussed how important
it is to use buffers, our
last week topic.
If you haven't listened to it,
please do.
I mean, not you, but our listeners,
right?
Because buffers are super important
and there are many aspects.
And I actually found pgMustard
as a good thing to visualize
and suggest.
It's not my idea, it's an idea
from my customer.
I personally think visualization
is not the strongest pgMustard
feature.
Strongest is a list of recommendations
based on heuristics, which
is quite a good thing.
So, and I know you improve it all
the time, already many years.
So it's great.
Yeah.
And what about me?
My company is now currently creating
bot.
Check out our latest, it's only
like a few months old, but our
blog post, my blog post, where
I described how we build it.
And currently we are rebuilding
it fully.
It will be interesting.
The release will be soon.
Yeah, it's quite interesting.
We bring it to web from Slack.
I think you will live in Slack
still, but it will be in web.
It's just easier to use it there.
And we started using different
models, including Gemini 1.5 with
1 million context window.
So yeah, we change our reg approach
right now.
And it's kind of interesting.
It knows already more than 1 million
documents about Postgres,
it's huge.
So yeah, that's probably enough
about Postgres.AI and pgMustard.
Then let's talk about limit.
Michael: Nice, yeah.
That's cool to hear.
Maybe we'll hear more about that
another time about the differences
between the models.
Yes, so I suggested this topic
and it came out of a tweet a long
time ago that I found myself recommending
over and over again
to people because it was a phenomenon
I saw and most people didn't
understand or didn't intuitively
understand, including myself.
So this was a tweet from Christophe
Pettus and the headline was,
limit considered harmful or something
along those lines.
And it was a really good kind of
initial tweet for a thread.
I was like, what?
Limit's good.
There's so many good things about
limit.
It's really helpful for so many
performance reasons.
What's harmful about it?
And he goes on to describe 1 specific
case where it can cause
plan flips and really, really slow
queries in cases where you
would expect it to be helpful in
performance.
So, that was the trigger for this
topic, but it might be nice
to discuss all the positives of
using limit Well, at least when
we should be using it whether we
should be using it that kind
of thing
Nikolay: Yeah, well first of all,
I guess we will be fighting
a little bit in this episode because
we have different opinions.
Let's start.
I cannot be on the side of Christophe
Pettus in that tweet.
I think there is huge potential
because limit, This is what every
one of us is using all the time,
right?
The limit is everywhere.
So if there are some cases when
it's harmful, it's interesting.
But my regular approach is if you
don't limit, you're in danger.
Limitless queries are harmful because
you don't know if you tested.
You know, my favorite topic, testing
and experiments and how
to test and the idea that if you
test on small databases, you
miss.
It's not, It's like it's bad testing.
It's anti-pattern unless you know
what you're doing and do it
before you test on full-size databases.
So this is exactly when it's not
even about performance.
It's just if you test on small
databases, small tables, and some
queries showing some results on
a page that don't have a limit.
And we start usually with it.
If we prototype something, first
we do it like forget about pagination,
Let's show everything here.
But then you test, it works well,
like 1,000 rows on 1 page is
not a problem, probably depending
on how heavy they are in markup.
But then project grows and some
users, for example, have 10,000
posts or, I don't know, comments,
and you show all of them on
1 page, and you have problems.
And this is a postponed problem
if you start a new project, right?
So limitless queries can be harmful
quite easily, and this is
a straightforward idea.
If you limit, at least you tell
yourself what to expect.
Okay, I expect 15 rows, for example.
Okay, we know it's good.
We know how many buffers we should
expect in the very efficient
query.
15 times the width of row plus
some internal buffer operations,
continuing the topic we had last
week.
Right, and that's it.
If you don't have limit, you don't
know what to expect.
Maybe it's a billion rows.
Michael: Yeah, so I agree and I
think pagination is quite a good
place to start because when people
think like if you look it
up limit in the docs for example,
you're also going to learn
about offset and it's a really
common concept.
Well yeah, so I'm a big fan of...
I'm going to say friend of the
podcast, Markus Winand, previous
guest.
Yeah.
I've always wanted to say friend
of the podcast.
So maybe I finally got to say...
Nikolay: It was a great episode.
Michael: Thank you.
And yeah, it was...
So he has a page on his website
called no offset and I just about
trying
Nikolay: to I just used the offset
hashtag on Twitter.
I use it all the time because when
we discuss when somebody mentions
offset and this is not an expert
who is doing offset on purpose,
knowing why, for example, index-only
scans before you deal with
regular index scan.
There you probably want offset
sometimes, but very rare actually.
So I always use this hashtag no
offset.
It's good.
Michael: I'm sure most people listening
know exactly what these
things do, but just for clarity,
When we use limit, we might
add limit 10 to the end of our
query, limit 20, limit some number.
And that will, as the name suggests,
limit the result set to
that many.
That many rows.
Nikolay: Limit 0.
I use sometimes.
Do you use limit 0?
Michael: No, but I've seen quite
a lot of offset 0.
Nikolay: I use limit 0 in 2 cases.
When I, for example, create a table,
I select specific columns
limit 0.
For example, sometimes just bootstrap
some table.
I don't want to repeat all data
types.
Sometimes it's like you're dealing
with CSV or something.
And also when I do tests, for example,
presence of column, you
can just select that column from
table limit 0, not caring about
anything.
And yeah, kind of assert.
So,
Michael: yeah, I've, I've seen
that really cool use case.
I've seen offset 0 used a few times,
so offset is, so limit limits
the first N results.
I've never thought of using 0 for
that.
It's cool.
Offset will jump you forward that
many before you start limiting.
So you could offset, you take the
first 10, throw those away,
and then limit will take you the
next 10.
So if you do limit 10, offset 10,
you get the second set of 10.
Nikolay: Yeah, and offset 0 it's
either some random generated,
right?
Because it was offset n where n
is 0.
Or it's also there was some performance
trick, some hack, right?
Yes.
I already forgot.
Michael: It's quite, yeah, I think
it's an optimization.
It is or was an optimization fence.
So it can, it can trick the planner
into not doing certain optimizations,
which I guess we'll get onto a
little bit, but there is a line
in the doc.
Nikolay: That trick or it was mitigated.
Michael: Well, something in the
docs made me think it might...
I think it's still...
I think they still work, because
I remember reading in a hacker's
thread, or it was in a thread somewhere
in the mailing list.
Nikolay: Yeah, certainly, it has
been used, but it was so long
ago, that's why I'm wondering if
it's still actual.
Michael: So I read a line in the
docs in preparation that the
offset 0 is the same as omitting
the offset clause, but I don't
think that's quite true because
of the planner implications, but
in terms of the results, that's
true.
In the results, you
Nikolay: won't change it.
Semantically, it's true, but physical
execution might be affected
with the presence of offset 0.
Michael: Yeah.
So the reason I brought this up
entirely was because we were
talking about pagination, right?
We can pack both common ways of
paginating a used limit.
1 is the kind of crude way of doing
it.
And a lot of our ORMs, a lot of
tools implemented it this way,
at least initially, was to the
first set of, let’s say, 20 rows
that you want, just add limit 20.
The next set, they just did offset
20, limit 20.
And then the next 1, offset 40,
limit 20.
And the problem with that is performance
degrades linearly as
you go.
And if you want to do a large,
you want to get to page 20 or
page 100, that can be quite an
inefficient query.
And Markus wrote a great post about
why we should instead be
doing key set pagination for predictable
performance and efficiency.
Nikolay: Right, its explanation
is simple, it's just because
internally it fetches everything
until that and discards.
Exactly.
It's like a lot of inefficiency.
It grows with the depth of your
originating, right?
Michael: So yeah, so what we were
talking about in terms of good
is where ID is above, like, where
some parameter is greater than
some, like, the last result you
fetched and then still limiting
by 20 or 21 depending on exactly
how you want to do.
Like that's something like that.
Nikolay: So limit 0 can be a problem
or limit 10 can be a problem,
right?
Let's talk about this problem maybe,
right?
Because I hope I already explained
why limit is good in terms
of performance, because you limit,
you understand yourself what
to expect, and you know what is
efficient and what's not if you
look at buffer operation numbers.
Michael: I also think there are
a couple of cases, like even
simpler cases, like it is, chances
are you don't want, like if
your query could return a hundred
thousand rows, do you really
want
Nikolay: all of them?
Right, right.
Michael: Or do you, you might only
be interested in the top 10
or maybe your user might only care
about the most recent 10.
And returning all 100,000, even
if the query is quite fast on
the server side, sending all that
data over the wire is inefficient.
So there are, I think it's kind
of like almost all more fundamental
reason that limit is good for returning
a subset of rows, exactly
what it's designed for.
Nikolay: It's close to our topic
about delete and batching.
By the way, someone asked on YouTube
comments, how about updates?
And I explained it's very similar,
but with additional things.
So if you don't batch and send
the whole result set in 1 shot,
what if not only just it's a lot
of memory and so on, but resiliency,
reliability, what if it fails in
the end of processing, you lose
everything, right?
And retry will be huge again.
So it's better to go in smaller
steps with retry logic.
And limit, of course, is the key
tool to split the batches.
So generation, batching, we need
it.
And also, in general, common sense,
if I limit again, like I
know how much I want, this is my
request, I want 15, for example,
or 2,500, and if you scan many
more parts of the database than these
15 records or 25 records plus additional
internal like index
pages and so on, then I'm not considering
this as efficient work,
right?
So it sets a simple expectation
and metric for efficiency.
Efficient versus not efficient
comparison of queries, right?
Michael: Yeah, I think that makes
tons of sense.
Like in application code, for example,
you are suggesting that
you're kind of communicating with
future developers what the
expectation is here.
I think this is where we're going
to start to differ in opinion,
though, because I think this is
exactly on the Let's say on an
ad hoc query, I've sat next to
people working on production systems
that were quite scared and saw
them and didn't think this was
a problem, actually was learning
from them at the time.
They were only expecting a single
row back and they added a limit
1 at the end of the query.
Nikolay: For example, primary key
lookup, right?
Michael: Yeah, I think this might
have even been like, I can't
remember exactly what they were
doing, but they definitely added
Nikolay: a primary key or a unique
key.
It cannot hurt because they are,
it's like if you don't trust
Postgres unique keys, you add limit
1 just as a sort, basically.
You think, okay, I must check,
it should fail or something if
it returns more.
Michael: Yeah, I actually can't
remember exactly why.
The thing I remembered was, oh
that's quite a clever way of not
having a runaway...
Nikolay: Actually, I remember I
did a different thing.
I did limit 2 on purpose.
So if something goes wrong and
I have 2 rows and I don't remember
where, but somewhere I should have
an error or exception, catch
it and process.
So limit 2 was...
Well, it should be unique, so I
expect 1 row, but I add limit
2 on purpose to check later that
it's not 2.
Michael: Yeah, I like it.
Nikolay: But yeah, so anyway, like
in my opinion, the limit is
always, I would add it like as
a must for everything.
I
Michael: think the fear was in
this case, a runaway sequential
scan across a huge table that might
start to eat resources and
cores.
I think this was a very, very on
fire server.
Nikolay: I'm thinking now and I
think maybe I would just even
consider this as a rule, mandatory,
like limit everywhere and
the only thing we cannot limit
is, for example, SELECT COUNT
* FROM something.
This basically deals with all rows,
returns just 1 number and
we cannot limit there, unfortunately.
There, okay.
Michael: I mean, you can add limit,
you're just still going to
get the count.
Nikolay: Yeah, limit will be useless
in this case.
Yeah, definitely.
Michael: The interesting thing,
though, and I was looking back
at just for fun, could I find a
version of the Postgres docs
that didn't have limit in it.
I'm sure it's as old as time.
But it was in an old version of
the docs, I got back to 7.1,
because that's the oldest one online.
And there was a really, like, I
got way more than I deserved
looking back at that.
And it said, as of Postgres 7.0,
the query optimizer takes LIMIT
into account when generating a
query plan, which I thought might
be a nice segue.
So before 7.0, there wouldn't have
been any danger in adding
LIMIT to your query.
I object.
Nikolay: Mind your language.
This language is wrong.
There is no danger in adding LIMIT.
Let's already jump to the core
of the topic.
There is no danger in adding LIMIT.
There is danger in having bad statistics
or expecting some wrong,
like forming expectations based
on indexes which cannot support
your ORDER BY with filtering and
LIMIT.
By the way, when you describe this
case, focus on ORDER BY as
well, because without ORDER BY,
this case is impossible.
It's not about LIMIT, it's about
ORDER BY plus LIMIT, which forms
expectations, like I mean, gives
planner the ability to use some
index, right?
Yeah, so let's
Michael: talk about, well, for
anybody thinking we've jumped,
I shared some examples with Nikolay
before the call, so that
he will talk about that.
I saw
Nikolay: this example before you
like a long ago.
I saw it live.
I just.
Michael: Yeah, well, that's good.
So, let's back up and try and explain
the problem or the edge
case or whatever you want to call
it.
So, exactly the same query, if
you run it with and without limit
some number, can be dramatically
slower with limit the number
added than it is without the limit
at all, which I found really
counterintuitive.
And yes, it relies on some specific
cases, but those specific
things are not as rare in the real
world.
Nikolay: It's not rare, I agree.
Michael: Yeah, so that person sitting
in front of that console,
hoping that limit some number is
going to make it safe and guarantee
fast execution.
Nikolay: Same logic is expected,
Yes, I agree.
Michael: Can actually shoot themselves
in the foot and make things
loads worse for themselves.
And I actually think there might
be other advice you could follow.
I'd much rather they added additional
where clauses or other
things than an additional limit.
So that's potentially controversial.
I'd love your take on it.
Let's try to explain how this can
happen.
So the simplest case is to limit
by a relatively rare condition
and then order by something else
that you have indexed.
So for example, created at.
So if we think about a, let's say,
a software as a service business,
and maybe you have a customer that
cancelled a few years ago,
and you want to look at their most
recent event or something
like that.
And so you're looking at by company
A, order by, created at,
descending, limit 1, something
like that.
Nikolay: Or limit 10.
Michael: Or limit 10, yeah, exactly.
Nikolay: So it can happen, the
problem can happen with not only
1.
It can be some small number.
Yeah.
Michael: It could be a big number.
Nikolay: Or relatively small.
The key is that it's small enough
to have a flip.
Michael: Yeah.
Well, Christophe made a good point
that I haven't tested yet.
He thinks it's worse around the
point of the actual number of
rows.
There are some cases where, let's
say that company had a thousand
events, the limit of a thousand
would be problematic.
So the problem comes from not knowing,
like Postgres assumes
things are randomly distributed
unless it knows better, unless
it has statistics to suggest otherwise.
And if it thinks that the table
that contains these events is
going to have events from any company
in any order, because it
knows it only has to fetch 1 or
10 events, it now has this optimization
where it might be quicker for it
to scan backwards through.
Nikolay: You missed a very important
thing.
Let me add things.
So we talk about, for example,
you took some SAS system, some
users, and users have some activities,
let's say, like comments
or, I don't know, orders, anything.
So we have orders table, for example.
This is what you showed, if I'm
not mistaken.
I remember.
So, orders.
And we have user ID, we have created
that, we have ID of order,
enough.
The key here is we want to select
orders for this specific user
in chronological order, like order
by created at desc, so like
in reverse order, the newest first,
the latest first, right?
And then the key here is not to
forget, our developers, or we
are developers, We don't know about
multi-column indexes.
So we created index on created
at, we created index on userId,
and on ID.
Okay, let's simplify assumption.
We like to index all columns.
It's worse.
And when we do it, we use only
single column indexes always,
right?
This is quite a classic example.
I can imagine it happens in the
life of every engineer during
5 years, for example, of development,
definitely at least once
it should happen, I would say.
99% of developers.
It happened with me many times.
And with my products, my databases,
and also with others when
I observe them.
Actually, recently we had a similar
case, maybe a few months
ago, I remember.
So, the key is Postgres needs to
choose, okay, I have filter,
user ID, and I have order by created
at desc limit 1 or limit
10.
And I have 2 indexes.
So I have 2 opportunities here
to save in terms of IO operations.
Of course, Postgres should avoid
sequential scan here.
It can be 1 user of many, of millions,
so it can be a huge table.
So we don't want to scan the whole
table, so we need to choose
only 1 index or 2 of them, maybe
combine with bitmap scan and
so on.
But 2 opportunities here, filtering
by user ID and also create
order by created at desc limit
1 or 10.
And the key here is order by.
Without order by, the limit, it
will be fast always because,
I mean, order by is the key to
consider index on created at.
Michael: Yeah, We need both the
equality check and the order
by to make it a choice for the
planner.
Either it has to filter on the
equality condition with one of the
indexes, take the data and sort
it and return the order, or it
has to choose the index that's
ordered and take the first row
it reaches that matches the equality
condition.
Nikolay: It can be not necessarily
equality, it can be between
one and the other.
Michael: It could be any, like,
yeah, so sorry, it doesn't have
to be, it doesn't also have to
be an order by the way, it could
be a different, it has to be two
separate conditions and those
have to fall.
Nikolay: They fight between each
other, right?
Yes.
How do they fight?
They fight based on costs, based
on statistics and what Planner
expects in terms of cost.
And the key also, like, important
point here is that Planner
has two costs.
Startup cost, which can be very
small, it means the first row
can be returned very fast.
And also the full cost.
Full cost is the most important
when Planner, like, the full
cost is what planner uses to make
the final decision choosing
between a variety of plans, right?
Full cost.
So our case, index on user ID,
if we use just it, it means Planner
thinks, okay, I will choose all
rows for this user, but then
I need to order by them in memory,
right?
It means startup cost cannot be
close to 0 because ordering and
so on, it will take some time.
And once done, only once done,
we can return all the rows.
I assume like in this case, startup
cost and the final full cost,
they are very close to each other,
but far from 0 probably, right?
But the other option, order by
created at desc limit 1 or 10.
This option means that we immediately
use created at index, we
start fetching rows based on there,
but we need to filter out
irrelevant rows owned by different
other users, right?
We only need our user ID.
It means we start returning rows
quite quick, but only if we
find our user ID quite quick.
There is uncertainty here.
But for Planner, it's quite certain
because it has some expectations
about distribution.
You mentioned it.
Probably, a planner can think,
okay, I will be lucky because
this user maybe is not real user.
Maybe it's still super active,
by the way, because we start from
the very fresh rows.
The latest row will be considered
the first and if our user ID
is there, it means we already found
one row in the first step already.
And we go, go, go.
But if this user is super inactive
last year, for example, in
reality, we need to scan the whole
year to reach the first row
of that user to return.
And here is exactly where the planner
can be very wrong.
I mean, it simply doesn't know.
It doesn't know.
There is no correlation in statistics
between user IDs and created
that.
And also, the planner doesn't know
who is actively active and
who is not.
The planner doesn't know.
So it can be very hard.
Michael: Yeah.
So there are multiple.
So I completely agree that you
brought up multi-column indexes.
In the case that I shared with
you and the case we just discussed,
a multi-column index on the user
ID or whatever it was and then
created that, ordered, would make
this query efficient and predictable
all the time.
And so, if this was an application...
Yes, yes, yes.
But if this is an application query,
and this was showing up
in pg_stat_statements as a problem,
and we needed to optimize
it, and it was one of our biggest
concerns, we didn't mind about
the overhead of adding another
index, great, we can add this
index, remove the one on user ID
or whatever we had, like, And
it would be fast all the time.
But I was talking about a case
where we're sitting in front of
a console on production trying
to stop ourselves from running
a large query or a slow query and
adding that limit, even though
we didn't think it would make a
difference, even though we think
we're only going to get one or two
rows returned, if we add it, we're
taking a risk that it could actually
make things worse.
Because we don't, like, if we don't
check, and this is actually
maybe what I would recommend.
I don't know about you.
But if you add explain before the
query and just check what index
it's going to use, does it make
sense?
If we saw it was going to do a
backwards scan on created at,
maybe that would be a sign that
it was a bad idea.
The tricky part is this is an issue
because of a cost underestimation.
So it's always going to look like
a cheap query in cost.
Like it, the reason it's being
chosen, if it's a bad plan, if
it was a slow plan, is because
the plan is thinking it will be
cheap.
It's because it thinks it can abort
early.
So costs won't give you a clue
here.
You'd have to be attuned to the
fact of which indexes it's scanning.
And is that enough?
Is that a good idea for this query?
So I'm talking about production
cases, like where you're doing
ad hoc queries, not so much application
queries where you should
optimize.
Nikolay: Yeah, for example, we
have to return our table and allow
users to order by all columns in
both directions.
It's a nightmare to support in
terms of performance.
And if you have many of such tables
or maybe you allow users
to create tables, any tables, and
you want to hide this performance
optimization from them, some website
builder or mobile app builder
and you can say, okay, users, developers,
your users are developers
of website or mobile app and you
say, I allow you to create tables,
I will take care of performance.
In tables, you're allowed to create
columns, I will take care
of performance.
And you can put some element on
your UI for your users, millions
of users, and you can allow them
to order by any column in any
direction.
It's a super nightmare.
Absolutely nightmare.
Yes.
Because obviously you want to create
index on every column and
also you think, okay, I'm smart,
I know about key set pagination,
you get your pagination, you then
allow this.
This is exactly where you can encounter
it, right?
Because this limit...
So I wanted to emphasize again,
like I described, 1 index has
very low startup cost.
It's creating that index.
And why is it low?
Because Postgres doesn't know for
arbitrary user.
We probably want this query to work
not for 1 user but for arbitrary
user, right?
So in this case, you probably think
Postgres doesn't know for
this particular user how fresh
activity is or orders are, right?
So maybe, but it assumes something.
It can be slightly more, slightly
less, depends.
But it assumes something.
For second row, it's 2 times more.
10 rows to 10 times more.
But once we find it, we don't
need to do anything.
We can traverse, create an index
and just filter out irrelevant
other users, right?
We just need to find our users.
Once we find our N, 1 or 10 rows,
we are happy.
This is our final cost.
And then if we don't use a limit,
our final cost is our expectation
for this user.
This statistic probably is present.
Probably, unless we don't have
statistics at all.
At all.
Postgres expects for this user ID,
I expect this number of rows,
and this forms our total cost.
Probably if it expects a lot of
rows, total cost will be high
and it won't start using this approach.
In this case, it switches to the
more reliable approach, I will
fetch all the rows and do a memory
sort.
So the key here, if we limit, this
is probably why Michael mentioned
that the boundary is this limit.
It's not the actual number of rows,
but its statistics, suppose
this expectation for number of
rows for this user ID.
So when our limit goes down, Below
it, below this number, our
traversal with filtering out irrelevant
users becomes more attractive
because total cost starts looking
good there.
But it's a trick, right?
Because the problem here, it's
a trick and of course, it's super
interesting phenomenon.
I would say this is a phenomenon.
It's not a harmful situation because
there are many cases when
limit doesn't help.
For example, we don't have indexes
at all, we say, well, no.
Michael: No, that can really help.
Nikolay: It can help, it can help,
yeah, yeah, yeah, because
filtering out, it can help.
But it's easy to imagine when limit
is useless in this.
Michael: Actually, that's another
time I use limit.
So the docs are quite
Nikolay: on the measure.
In general case, it improves performance
because you will stop
earlier.
But on edge case, when our record
is the latest in scanning.
It doesn't help.
Here we deal with a similar situation.
In general it's good, but we have
some edge case which not only
doesn't help, but it decreases
performance.
But it's an edge case and it's
phenomenal.
It's not a harmful limit, especially
because you cannot do this
without order by.
And especially because the problem
here is not limit, but lack
of proper index to call an index.
Michael: And again, we're talking
about a one-off query rather
than an ongoing optimization challenge.
I'm talking about it's just an
interesting phenomenon that, and
I don't think most people expect
this, that by adding limit you
can make the query slower.
And that's true.
It can be a lot slower.
So I just don't want people to
use that as their safety mechanism.
Nikolay: You know, when you understand
what's happening inside,
adding limit is clear why it's
wrong.
It's clear.
You just force the planner to choose
the wrong index here.
Right?
That's it.
But of course for people who don't
dive inside the execution
and planning process, they of course
are very surprised and think
limit is bad.
But remove order by and you won't
see this effect, right?
Michael: But the docs, and this
is what I wanted to say, the
docs quite often when you're reading
about limit will say it's
really sensible to use order by
with limit because otherwise
you're getting things in a like
an undetermined
Nikolay: Again, 1 second.
If we talk about arbitrary user
ID, what if this user ID is super
active and created a thousand orders
right today?
In this case, it's right.
So that's why I say this is not
even an edge case, it's a corner
case.
You have several things in the
corner here.
And observing various strange effects,
phenomena, of course,
it makes sense.
But create proper indexes, that's
it.
But at least let's correct this
harmful title.
Let's say limit 1 with order by
considered harmful.
Order by limit.
Michael: It's not just order by,
though.
It could be another condition that
you add.
Nikolay: Lack of multi-column indexes
order by limit considered
harmful.
This is the full truth.
This is the truth actually here.
Not just limit.
Michael: I was hoping you'd understand
my point of view on this.
It feels like you haven't.
I do think that in a lot of real
world cases, people don't have
perfect stats or they have data
that's correlated strongly in
certain ways.
For example, like user ID and account
ID being extremely tightly
coupled.
Or you know, the age old city and
country codes.
And Postgres assumes that all of
these stats are uncorrelated
by default.
And if you don't, you can add,
you can create statistics for
these things.
But in a lot of cases, we have
a lot of skewed distributions
in databases that we haven't created
extra statistics for.
And most of the time, the plan
is great and can really can work
things out nicely.
But by having this limit optimization,
it can flip plans that
we're not expecting to flip.
And the reason I found it so powerful
is we're using it exactly
in the cases where we're most scared
of doing a big scan on production.
If that's what you're really scared
of doing, And this is something
that can
Nikolay: be quite...
Why do you not agree with all this
concept that is harmful?
Because imagine we don't have an
index on user ID at all.
In this case, if...
In this case, created that shows
bad performance because the
user became inactive last year,
we need to scan the whole year.
But at least we have some approach
to scan, right?
Created that adds some order to scanning.
Without it, we only have a sequential
scan, right?
That's it.
So the problem is we don't have
a proper index.
Now, okay, it turns out we have
an index on user ID.
It happened to be helping, but
what if some user has 50% of whole
row like records, millions of records.
Michael: So bear with me.
This is why I'm suggesting instead
of running the query on production
by adding a limit 1 or something
to make it feel safe, instead
of adding limit 1, put just 1 word,
explain, in front of the
query, not explain analyze, not
explain analyze buffers, just
explain your query without the
limit and see what the query plan
is before you run it.
If you're scared of what it's going
to do in production, do that
instead of adding the limit.
And you'll see which indexes it's
using, whether you're
Nikolay: missing one or whether you're
not.
But I cannot add limit because
I don't want my page to blow up
because of many, many rows returned.
ALEX LUCASSE-COLOMBRE-CAMPOYS
Michael: Well, add limit 1 and
put explain in front just to check
what it's going to do.
Because that's what's really going
to check.
That's what's going
Nikolay: to actually keep safe.
SLAVOJ ŽIŽEKERJÁNAS You will check
the player behavior, right?
ALEX LUCASSE-COLOMBRE-CAMPOYS
Michael: Yeah, you're checking
what you're actually scared of
instead of assuming you're going
to be safe.
It's like, I understand your point
that it's almost never going
to be a problem, but imagine if
a safety on a gun, for example,
almost always kept you safe.
It's like a safety feature that's
almost always good, but sometimes
it's going to actually shoot you
when you press that safety catch.
Not when you pull the trigger,
but when you press the catch.
Nikolay: Again, in my world, I
add limit 1 to limit things.
If it doesn't work, it means I
don't have an index.
If there is some index which would
help me when I don't have
a limit, I don't care because I do
need limit 1 anyway.
Michael: And maybe you'll hit some
like statement timeout or
you'll cancel your query quickly
or there was some other safety
mechanism in place that will stop
you from hurting yourself.
Nikolay: But still, if my limit
behaves not good, I'm looking
which index will help.
And I'm not considering index on
user ID as helpful here because
I don't want to deal with a lot
of records and memory because
I need only 15 records.
So that's why I jump straight to
multi-column indexes here.
And as we discussed...
Michael: I agree.
I agree that to optimize this query,
but we're not talking about
optimizing the query, we're talking
about should you use limit
as a safety feature when you don't
need it?
Nikolay: Why I don't, why might
I not?
Michael: Let's say you know there's
only going to be 1 row, should
you add limit 1.
Nikolay: How do you know it's only
1 row?
Unique index?
If you have unique index, this
problem won't happen.
Michael: Let's say a company could
set up multiple things, but
they generally set up 1.
Like how many websites are you
gonna have for each organization
you don't have a unique index on
it people can add a second okay
but almost everybody else for
Nikolay: you for example right
Michael: yeah email addresses yeah
it's probably gonna be less
than 10 like you don't have any
you know but you maybe you're
not certain it's going to be 1,
but you're pretty sure it's going
to
Nikolay: be 1.
But am I having up-to-date stats
or no?
Because stats will say, they will
form an expectation to Postgres
if it's a low number of rows, it
would prefer.
Michael: I think I'm struggling
because it's a simplified example,
but if you've got lots of joins
going on there could be all sorts
of other like complexities.
Nikolay: Joins are tricky by the
way.
The same problem is becoming much
more tricky but I don't think
limit this problem.
I think the problem is a lack of
proper indexes and sometimes
you cannot create them.
Imagine user id is present in 1
table but created that is present
in different table.
Michael: So you're saying you've
never sat in front of a database
where you don't know all of the
indexes in the system, you don't
know the state of stats and wanted
to run a query and not being
scared that it might take a long
time.
Nikolay: Well, when I do this,
I add limit, I understand that
if I lack proper index, that will
help.
I might be dealing with sequential
scan or wrong index have been
scanned almost fully, right?
I understand that.
If you don't understand that, that's
strange.
Right?
Michael: Well, but then, so this
is my, that's kind of my, like,
takeaway.
Instead of adding limit 1
Nikolay: and hoping that I'm okay.
Let me give you an example I saw
before what we discussed today.
So we have full-text search, for
example.
And we have rare words and we have
very frequent words present
in the majority of records in TSVector.
Yeah, that's a nice example.
Right.
And then we have created that OR
ID and we want to find all matching
documents and return only 15 latest,
for example.
In this case, Postgres must choose
between need to choose, must
choose between ordering by bit
3 and then applying a filter to
add science, right, TS vector,
add TS query.
Or it might decide, okay, I need
to choose, I need to extract
all documents and then order by
memory and then limit.
Same problem, completely same problem.
And I saw terrible cases when I
thought, wow, like, why do you
do this?
Why do you think this word is super
frequent?
So instead of using gin, you decide
to use B-tree to order by limit
15, for example.
And then you end up scanning almost
everything, finding my 1
or 2 records only in the end of
scanning.
It's absolutely the same situation.
But the problem to me is not limit
there at all.
I put limit, I see the problem,
I understand.
Limit didn't help, I understand
why.
Because we need to combine both
things in 1 single index and
have only 1 single index scan.
Unfortunately, this is impossible
there, right, in this particular
case.
Because for that you need to use
a RAM index and it can be not
available.
Nardius, for example, it's not
present.
Michael: Or like work out some
middle ground, take way more rows
than you want and then sort them,
that kind of thing.
Nikolay: Right, so I mean We sit
on the edge here.
We might be lucky, but we are not.
And what else?
What can I say?
It's not a problem of limit, it's
a problem of lack of indexing.
Michael: Let's try and wrap up.
And I think I agree with you that
to solve the performance issue,
you don't get rid of the limit,
you add a good index or you get
your stats, like you work out your
stats correlation, maybe create
some statistics there.
Maybe you just need to run analyze
on that table or maybe globally,
like that.
There's lots of ways of solving
it so that that query doesn't
run slowly again in future.
I just wanted to publicize and
share that.
And by the way, I wanted to shout
out Frank Pashoe, who got in
touch recently when I shared this
Twitter thread again, to say,
I think we agreed that it's
an optimization problem.
And yeah, he would completely be
agreeing with you right now.
I just found it really interesting
and quite counterintuitive.
Like probably it sounds like more
than you did, that it could,
like just by adding it could make
a query slower.
Nikolay: Right.
Michael: Even though that's the
sign of other issues.
So I wanted
Nikolay: to clarify that.
It's an interesting phenomenon,
for sure.
And adding a limit is not a performance
optimization tool.
Performance optimization tool is
order by limit and improper
index, like together.
Yes.
And at the limit, I should not
expect quick execution.
I just form expectations for myself
and I can then say, okay,
this is not what I want and find
a better index, which is sometimes
not possible, for example, GIN
versus B-tree and so on.
But by the way, to be fair, with
GIN statistics already, Postgres
is quite often right, so it knows
when to use B-tree and quickly
find proper results, and it knows
when it's better to find everything
and order by memory.
So it's not that bad, but also
not ideal, not just single index
scan.
Michael: Yeah, cool.
Nikolay: Okay.
Michael: Thanks so much, Nikolay.
Take care.
Nikolay: Thank you.