►
From YouTube: 2019-07-04 Database: Query optimization
Description
In this video, I look at a rather complex query along with a proposed change. We figure out the difference this change makes and find ways to drastically improve the overall query performance.
We also touch on two rather typical query optimizations: Partial indexes and UNION over OR. This brings the query runtime down from 5,000ms to less than 100ms in our example.
Andreas Brandl - Sr. Database Reliability Engineer at GitLab: https://gitlab.com/abrandl
---
This is the MR we are looking at: https://gitlab.com/gitlab-org/gitlab-ce/merge_requests/28887#note_185007453
We started to discuss about this complex query in https://www.youtube.com/watch?v=s70JhO1zt_8&t=2469s and we were wondering how to approach this from a reviewer perspective. I hope this recording gives an idea how even complex queries can be approached.
A
Alright,
so
in
this,
video
I
would
like
to
look
at
the
nammar
that
comes
with
a
curry
change
and
when
we
look
at
the
full
query
that
queries
rather
complex
and
we've
discussed
that
and
better
ways
off
as
I
was
recently
how
to
how
to
approach
that,
when
there
is
a
very
complex
curry,
how
to
figure
out.
If
that
changes
is
actually
good
or
if
we
need
to
make
improvements
and
I'm.
Just
sharing
my
screen.
There.
A
A
So
all
we
do
here
is
adding
columns
adding
explicit
columns
instead
of
the
Select
star,
which
is,
which
is
always
a
good
idea,
and
the
actual
change
is
at
the
bottom,
where
we
aim
where
we
join
users
and
project
authorizations
against
the
the
other
relation
and
what
we
could
already
tell
this
since
those
are.
This
is
the
full
query
as
well,
so
the
full
queries
rather
complex.
A
We
can.
We
can
look
at
that
in
more
detail,
but
there
is
even
there
is
even
a
CTE
inside
you
can't
even
see
at
the
moment
there
is
a
recursive
CTE
which
is
sort
of
a
sub
query
inside
that,
and
the
whole
thing
looks
quite
impressive.
I
would
say,
and
when
you
approach
that
as
a
reviewer,
there
are
two
sides
you
can
or
two
perspectives
you
can
have.
One
is
that's.
The
change
makes
sense.
Is
the
performance
impact
of
that
change?
A
A
Think
it's
most
important
to
look
at
the.
What
the
difference
is
that
this
change
introduces,
because
it
might
just
change
a
part
of
the
query
that
doesn't
make
it
worse
but
doesn't
improve
the
Korean
it's
on,
and
that's
probably
okay
too,
and
the
bigger
perspective
you
can
have
as
a
bad
queries
actually
rather
complex.
A
Is
there
a
way
to
improve
that
and
I'll
start
with
the
first
perspective,
so
just
figuring
out
what
the
changes
actually
and
then
we
can.
We
can
look
where
that
goes
so
I
asked
if,
if
we
can
have,
if
we
can
have
the
curry
before
enough
that
I
change,
because
that
makes
it
really
much
much
easier
to
look
at.
So
we
have
this
raw
sequel
query
here.
This
is
the
new
query
and
then
below
here
we
have
the
old
curry
and
what
I
like
to
do
is
usually
I.
A
A
A
A
A
A
All
right,
let's
take
a
look
at
the
tip.
Hopefully
that's
the
same
now
right.
We
still
have
the
same
differences,
but
they
those
were
easy
to
print
on
all
right.
Then
I
would
go
ahead
and
grab
the
query
plan
from
a
production
replicas.
The
way
I
do
that
is
I
I,
actually
log
into
a
replica
and
jump
on
a
piece
equal
session.
A
So,
even
though
it's
interesting
to
look
at
the
cold
cache,
behavior,
ie
I
run
them
a
bit
more
often
together
to
get
a
feeling
of
that
actually
changes
things
and,
as
we
could
see,
the
runtime
is
bit
better
now.
But
it's
still
like
it's
three
point:
six
seven.
So
you
see
three
point
six
seconds
and
running
that
again:
yeah
blends
in
the
same
same
ballpark,
I
guess
so
that
is,
that
is
a
good
plan
for.
A
The
old
Korean
I
guess
so
I'll
just
grab
that
when
you
use
chat
ups,
there
is
also
an
option
to
get
a
graphical
representation
of
that,
and
you
would
actually
get
a
link
like
this
and
that's
that's
a
good
way
to
look
at
that.
Well,
just
take
a
note
here
and
put
the
plan
in
their
comments,
and
then
we
do
the
same
thing
for
the
new
version
of
that
green,
so
explain,
analyze.
A
A
A
So
this
is
the
the
old
version
of
that
query
and
then
for
the
same
parameter
values
of
the
the
new
version,
and
what
we
can
already
say
is
that
the
the
change
that
we
introduced-
you
know
the
change
from
DMR-
was
explicit
column,
names
and
then
joining
to
two
other
relations
in
addition
to
the
existing
ones
and
that
that
change
actually
makes
it
worse
run
time
voice
rise.
So
we
go
from
three
point:
seven
seconds
to
about
five
seconds.
A
Now,
what
we
can
look
at
an
on
just
going
to
start
with
the
old
version
of
that
Korea
I
would
like
to
understand
better
what
where
we
spend
most
of
the
time
and
what
is
obvious
when
you
through
when
you
scroll
through
that
plan,
is
there
is
a
note
here
that
basically
eats
all
the
runs
when
it
takes
3.7
seconds.
That
is
the
majority
of
run
time
for
that
query,
and
that
is
being
consumed
from
this
particular
node,
and
we
can
also
see
that
even
excluding
all
the
notes
below
that
right.
A
So
this
is
the
exclusive
column,
even
excluding
those
the
run
friends
not
much
different.
So
for
what
it's
worth
the
index
scan
here
actually
is
the
major
contributor
to
the
run
time,
and
it's
also
a
quite
typical
example,
where
we
use
an
image
scan
on
the
members
table
here
and
as
a
condition
we
put
in
the
source
type,
so
source
type
equals
namespace.
That
is,
that
is
what
the
index
can
do
for
us.
A
In
addition
to
that,
we're
also
interested
in
filtering
on
other
conditions-
and
that
is
in
white
token,
is
an
all
type,
is
group
number
and
then
there
is
even
like
a
or
condition
in
the
end
which
which
goes
to
the
sub
plan.
So
here
we
we
have
two
things
that
we
hash
from
from
our
sub
plans,
and
then
we
figure
out
on
the
fly,
if
our
record
that
we
retrieve
here
satisfies
just
those
conditions
too,
and
a
good
thing
to
look
for
is
actually
rows
removed
by
filter.
A
We
can
see
here
that
this
filter
right,
so
this
filter
here
removes
more
than
two
million
rows
and
the
index
would
sort
of
return
quite
a
few
like
a
few
million
rows
and
that
further
removes
most
of
them
and
we
would
only
produce
seven
hundred
something
rows
as
a
result
from
that
and
next
scan.
So
in
a
sense,
the
the
index
is
not
quite
efficient
for
for
that
query,
and
we
could
think
about
improving
that,
but
that
index
or
maybe
providing
a
different
one.
A
Now,
let's
figure
out,
if
that
is
also
the
case
for
the
new
version
or
like
query,
and
as
we
can
see
here,
this
is
the
corresponding
index,
scan
nearly
the
same
numbers
right
and
also
rows,
remove
a
filter
similar
to
the
other
query,
so
that
is.
That
is
definitely
the
same
here
now.
Where
does
all
the
whether
we
spend
all
the
other
time
that
is
that
was
introduced
from
from
the
change?
A
A
All
right
so
taking
a
step
back
I
would
I
would
like
to
see
if
we
can
fix
that
part
and
a
way
to
do
that
is
actually
creating
an
X
that
helps
here
so
that
that
actually
satisfies
a
few
of
those
conditions
so
that
we
don't
have
to
remove
those
rows
on
the
fly
anymore.
And
when
you
create
an
index,
you
can't
do
that
on
a
production
replica,
because
that
is
that
is
read-only,
and
you
know
it's
probably
fine
to
to
get
a
query
plan
for
a
select
query
on
production
replicas.
A
But
you
you
don't
want
to
create
an
index
just
to
figure
that
out
so
the
way
I
I
have
to
do.
That
is
we.
We
can
create
production,
copies
or
copies
of
the
production
database
and
we
spin
them
up
in
separate
instances,
and
then
we
can
actually
promote
them.
Read
right,
so
I
have
have
a
full
production
copy
in
a
separate
instance,
and
I
can
use
that
to
actually
mess
with
a
schema
or
to
write
to
it
to
make
some
changes
there
without
affecting
the
production
database.
A
A
A
Surprisingly,
it's
actually
faster
than
on
the
production
since,
but
we're
to
know
that
the
instance
we'll
be
working
on
is
not
serving
any
other
production
or
traffic.
So
that
might
be
a
part
of
the
reason
why
I
thought
it's
different
just
on
my
phone
alright,
but
that
gives
us
an
idea
of
the
execution
time
here.
So
this
is
about
one
second,
let's
quickly
see
if
we
can
spot
the
problematic
part-
and
this
is
the
index
scan-
oh.
A
A
A
A
Right
and
that
is
consistent,
okay
and
then
we
see
this
is
actually
the
index
that
we
were
also
seeing
on
the
other
plans,
and
we
spend
a
lot
of
time
filtering
out
those
2.2
million
records.
So
now
let
us
create
the
index,
and
this
is
a
bit.
Okay.
I
just
dropped
one
of
those,
maybe
not
too
surprising,
but
in
this
case
we
still
want
to
have
source
type.
This
is
what
the
current
index
already
provides
and
I'll
just
make
that
a
partial
index,
because
this
is
something
we
can.
A
A
A
All
right,
then,
that
went
down
from
2.7
seconds
to
one
second
roughly,
and
we
can
make
sure
that
the
change
is
actually
actually
successful
and
yura.
That's
the
case
we'll
be
using
the
index
that
we
just
created
and
the
filter
condition
is
also
down
so
notice
that
the
conditions
we
put
on
the
partial
index
are
gone
here.
A
This
is
because
we
directly
use
that
index,
so
we
don't
have
to
do
any
additional
filtering,
but
the
what
is
left
is
basically
the
those
two
plans
or
you
know
the
those
two
condition
they
come
from
the
sub
plan,
where
we
have
an
unrelated
index
scan-
and
you
know
another
rather
complex
query
over
here-
and
this
is
actually
where
the
majority
of
records
are
being
filtered
out.
So
this
is
still
1.8
million
as
compared
to
two
point
you
before,
so
it
was
better.
A
A
That's
probably
applicable
for
the
new
version
of
the
query
too,
just
to
make
sure
that
it's
the
case
run
and
explain
analyze
on
the
new
version
too,
and
we
should
basically
see
the
same
thing
here
and
we
do
so.
This
is
the
index
scan
on
members
without
newly
created
index,
and
they
did.
The
rest
is
the
same.
Basically
as
in
the
old
version.
A
So,
taking
a
step
back,
the
questions
really
I
mean
those
runtimes
are
not
great,
so
even
the
the
old
version
of
the
queries
one
second,
this
is
2.5
seconds
still
something
that
we
would
want
to
improve,
especially
those
are
part
of
the
rep
requests.
You
don't
want
to
wait
for
one
or
one
button
or
two
seconds
for
for
those
to
complete,
so.
A
A
A
We
basically
are
interested
in
members
right
so
with
some
conditions,
and
then
we
look
for
the
members
ID-
and
this
is
this-
is
typical
for
rails
finders,
that
once
we
have
your
filter,
our
members
and
then
you
say
members
ID
is
in,
and
then
you
put
another
query,
selecting
members
IDs,
which
doesn't
really
make
sense
from
a
sequel
perspective,
because
you
just
you,
could
get
rid
of
that
indirection
and
luckily
the
post
ghost
planner
takes
care
of
that.
Most
of
the
time
still
looks
very
done
on
sequel.
A
A
The
question
is
or
how
we
could
improve,
that
is
actually
we
we
can.
We
can
select
which
we're
just
interested
in
selecting
member
IDs
here
and
basically
this
is
two
sets
of
member
IDs.
One
is
the
one
that
comes
from
this.
This
rather
simple
select
right,
so
we
look
for
probably
all
the
members
in
that
group,
and
then
there
is
the
other
more
complex
part
which
also
just
yields
member
IDs,
which
sort
of
traverses
to
hierarchy,
to
extract
that
and
the
way
we
do
that
is
we
or
those
conditions.
A
So
we
have
to
conduct,
or
we
have
one
set
and
I'm
another
condition
where
we
say
or
if
the
members
IDs
in
in
this
other
set
and
okay
include
that
as
well.
What
we
could
do
instead
is
actually
use
Union
and
sequel,
because
Union
provides
a
way
to
merge
to
two
relations
and
in
the
sense
this
is
a
relation
that
gives
us
member
IDs
on
the
other
ones
as
well.
So
why
don't?
We
merge
those
those
two
relations
with
Union,
so
let
me
see
if
I
can
figure
out
how
to
insert
that.
A
So
basically,
we
would
replace
this
with
the
and
now
I
just
have
to
get
the
parents
fixed,
I,
guess
alright,
but
that
looks
fine.
So
what
we
now
do
is
we
select.
We
have
one
basically
one
sub
query
here,
which
is
a
union
query,
and
this
is
the
first
part
of
the
Union,
and
this
is
the
second
part
of
the
Union
right.
A
So
I'll
grab
the
query
and
look
at
a
plan
for
it
and
notice.
This
is
the
the
new
version
we
were
working
on
the
old
version
right,
so
not
to
confuse
that,
but
the
the
previous
run
time
of
the
old
version,
without
that
Union
change
was
about
one
second,
and
now
we
can
to
explain
analyze-
and
this
is
actually
much
faster
than
all
like.
This
is
at
70
milliseconds.
A
A
A
A
A
There
is
still
one
thing
we
can
think
about,
and
that
is.
There
is
two
different
ways
to
do.
Union
Union
basically
merges
two
relations
with
the
same
columns
and
it
would
get
rid
of
any
duplicates
so
just
doing
select
something
unions
like
something
else
actually
performs
removing
duplicates
and
what
we
can
do
here,
because
removing
those
duplicates
may
or
might
not
be
good.
You
can
just
see
if
there
is
a
difference
with
Union.
A
All
so
Union
all
doesn't
care
about
the
duplicates
anymore
and
that
actually
my
arc
that
improves
maybe
a
bit,
but
it's
also
questionable
because
if
that
would
become
a
very
large
result
of
that
sub
career.
Because
of
that,
because
there
are
so
many
duplicates,
it
may
also
become
a
problem
for
the
for
the
bigger
career
outside
of
that,
I
would
actually
like
to
see
that
I'm
on
production,
because
we
can.
We
can
actually
do
that
because
we
realize
that
we
don't
need
index
so
I'll
close
my
rewrite
copy
and
just
to
make
sure
I.
A
Don't
forget.
This
is
production
just
looking
about.
So
this
is
the
production
replica
right
and
now
I
can
run
that
old
version
of
the
query
and
it
actually
is
very
much
faster
than
before,
like
38
milliseconds
and
the
whole
thing
is
done
as
a
reminder.
We
looked
at
those
plans
and
the
old
version
of
that
query
performed
in
3.7
seconds,
so
that's
quite
good
I
would
say
that'll
speed
up
and
now,
let's
figure
out
if
that
also
applies
to
the
new
version
of
light
gray.
A
A
And
we
run
the
new
query
and
even
though
there
was
this
additional
join
to
the
users
table,
it
now
goes
down
to
a
little
bit
below
100
milliseconds
compared
to
five
thousand
milliseconds.
So
that's
quite
good
and
with
the
additional
join
the
runtime
is
still
accept,
acceptable.
I
would
say
at
least
it's
much
better
than
before.
Where
run
time
went
up
from
three
point:
seven
to
five
seconds.
A
Okay,
so
as
a
quick
summary,
we
looked
at
the
complex
query:
we
formatted
it.
We
dipped
it
to
make
obvious
what
the
what
the
differences
are.
We
got
those
career
plans.
They
were
in
the
the
seconds
range
on
the
production
replicas,
so
some
somewhere
between
four
and
five
seconds
for
any
of
those
versions.
So
the
old
on
the
new
version.
A
We
spotted
those
the
next
problem
where
a
lot
of
rows
were
removed
on
the
fly.
Are
we
created
an
index
for
that?
Only
to
realize
that
oh,
this
was
improving
a
bit,
but
not
not
that
there
was.
There
was
still
room
to
improve
other
where
otherwise
we
spotted
the
Union
all
opera
or
union
opportunity.
So
we
replaced
the
oral
condition
with
the
Union
and
then
run
time
even
with
the
additional
join
went
down
from
five
seconds
to
below
100
milliseconds.
A
So,
in
a
sense,
this
is
sort
of
totally
unrelated
to
the
mr
at
hand.
So
it's
really
just
improving
the
overall
query
and
I
would
I
would
sort
of
think
that
we
want
to
ship
the
Union
all
change
here
and
then
we
can
also
ship
the
change
that
we
were
looking
at
because
it
doesn't
make
run
time
much
worse,
anymore,
I
hope,
recording
that
was
sort
of
useful.
There
are
any
questions,
feel
free
to
drop
them
or
reach
out
to
me
and
I'll
see
you
next
time.