From the course: Advanced SQL for Data Scientists
Join today to access over 22,600 courses taught by industry experts or purchase this course individually.
Solution: Choosing an optimal indexing strategy
From the course: Advanced SQL for Data Scientists
Solution: Choosing an optimal indexing strategy
(bright music) - [Instructor] We have really two options we can think of. Now, by default, when we build an index in Postgres, we use a B-tree index. Now B-tree indexes are generally good choices because they're relatively fast to work with because, on average, you're going to get a time basically relative to the logarithm of the size of the table, so even a table with a large number of rows is going to be able to find the index value relatively quickly. Now because we're working with claim IDs and claim IDs are unique, that means that at most, one row in each table will have that particular claim ID. Well, in that case, since we're not going to be doing things like range scans or things like that, which work really well with B-tree indexes, we can use a hash index. A hash index uses a function that takes a column value, like a claim ID, and then converts it into a 32 bit integer, and we can use that 32 bit integer…
Practice while you learn with exercise files
Download the files the instructor uses to teach the course. Follow along and learn by watching, listening and practicing.
Download courses and learn on the go
Watch courses on your mobile device without an internet connection. Download courses using your iOS or Android LinkedIn Learning app.