Indexing Materialized Views

[00:00:00]
>> 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.

[00:00:39]
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.

[00:01:11]
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.

[00:01:45]
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.

[00:02:09]
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?

[00:02:30]
>> 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.

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