Check out a free preview of the full Complete Intro to SQL & PostgreSQL course

The "Indexing Materialized Views" Lesson is part of the full, Complete Intro to SQL & PostgreSQL course featured in this preview video. Here's what you'd learn in this lesson:

Brian adds an index to the materialized view which leads to a four million percent increase in performance.


Transcript from the "Indexing Materialized Views" Lesson

>> So, how do we get this to be even faster? This is still pretty slow. Indexes, surprise, surprise, our old friend the index. CREATE INDEX idx_actor_categories. ON actor_categories. (count DESC NULL), And I don't think there's actually any nulls in this cuz it's just counts, but if you want to you can put nulls last, so then you are always sure of that.

Or not, up to you, I'm gonna put it in there. You can leave it out if you want to. Okay, now I have an index on that, so let's run that same, This descending, because I put NULLS last here, I have to put NULLS last here. Even though that would make a difference here, it won't use it because it doesn't see them being the same index.

But let's run this again, this was 88,000 up here, let's see what this is now. This went from 88,000 to 0.75. That's a slight increase in performance. Even just from the 88,000 up here, but think of it from the 290 something thousand, this one right here, almost 300,000 up here.

So I'm not a math guy but that seems pretty good. Or I did the math for you, that is a 4,000,000% increase in performance. You should get promotion for that, I think, or at least a pat on the back. A really firm one though that makes you feel good, one of those.

Okay, any questions about what we looked at?
>> Besides how expensive they are to make, what would be the downside of just having a ton of indexes? Say you have the downtime, you can do all that, is it like an organizational thing? Why wouldn't you just index as much as you could?

>> Space.
>> Just space, okay.
>> Space is number one, number two is it does slow down the planner, right, because the planner has to go through every index, can I use this? Can I use this? Can I use this? Can I use this?
>> Gotcha, okay, that makes sense.

>> Yep, Those two things, I guess three is, you should keep it tidy house, right? Yeah, that is, views, and materialized views.

Learn Straight from the Experts Who Shape the Modern Web

  • In-depth Courses
  • Industry Leading Experts
  • Learning Paths
  • Live Interactive Workshops
Get Unlimited Access Now