Lecture 3: Disk Management and Indexes#
Gittu George, January 16 2024
Todays Agenda#
HOW do humans store and search data?
HOW does Postgres store data?
HOW does Postgres search data?
WHY indexes?
WHAT are indexes?
Different kinds of indexes
Do I need an index?
Learning objectives#
You will apply knowledge of disk management and query structure to optimize complex queries.
You will understand the different types of INDEXes available in Postgres and the best-suited applications
You will be able to profile and optimize various queries to provide the fastest response.
You already have the data we will use in this lecture from worksheet 2, but if not, please load it so that you can follow with me in lecture (You can load this data to your database using this dump. It’s a zip file; do make sure you extract it.). I will use the testindex table from fakedata schema for demonstration.
How do humans store and search data?#
Think about how we used to store data before the era of computers? We used to store the data in books. Let’s take this children’s encyclopedia as an example.
.
There are around 300 pages in this book, and if a kid wants to know about China, they will go from the first page until they get to the 48th page`. There might be other places too that talk about china. So the kid wants to go over all the pages to gather all the information about China.
This is how a kid would search, but we are grown up and know about indexes in a book. So if you want to read about china, you would go to the index of this book, look for China, and go to the 48th page straight away (and to other pages listed in the index). If you want to read some history about indexes, check out this article.
OKAY, now you all know how humans search for data from a book; Postgres also store and search data similarly.
How does Postgres store data?#
Information within a database is also stored in pages. A database page is not structured like in a book, but it’s structured like below.
.
So these pages are of 8 kB blocks; there are some meta-data as headers (green) at the front of it and item ID (yellow) that points to the tuples(rows). This ends with a special section (blue) left empty in ordinary tables, but if we have some indexes set on the tables, it will have some information to facilitate the index access methods.
So a table within a database will have a list of pages where the data is stored, and these individual pages store multiple tuples within that table.
Read more about it here.
How does Postgres search data?#
Let’s consider a complicated table.
For explanation, let’s assume that the contents in this table are stored in 3 page files. Here we are interested in finding the rows with the unique name that starts with y. A database, just like a kid, needs to search through the 3 pages to find the rows that contain a name that begins with y. This is a sequential search, and this is how a database search by default. This process could be slow if we got millions of rows spread across multiple pages, as the computer needs to go through the entire pages to find the names that start with y.
Also, here in this example, let’s assume that Yokamino is on page 2, the database will find this on page 2, but it will still go to page 3 in search of other occurrences of names that start with y. WHY? Because it doesn’t know that there is only a single occurrence of a name that starts with y.
Let’s look at how Postgres search for data using EXPLAIN. We will do this on table fakedata.testindex,
import pandas as pd
import matplotlib.pyplot as plt
import psycopg2
import json
import urllib.parse
%load_ext sql
%config SqlMagic.displaylimit = 20
%config SqlMagic.autolimit = 30
with open('credentials.json') as f:
login = json.load(f)
username = login['user']
password = urllib.parse.quote(login['password'])
host = login['host']
port = login['port']
%sql postgresql://{username}:{password}@{host}:{port}/postgres
'Connected: postgres@postgres'
%%sql
DROP INDEX if exists fakedata.hash_testindex_index;
DROP INDEX if exists fakedata.pgweb_idx;
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
Done.
Done.
[]
Here, we do a normal search for a product name within the column productname.
%%sql
explain analyze select count(*) from fakedata.testindex where productname = 'flavor halibut';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
10 rows affected.
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=144310.50..144310.51 rows=1 width=8) (actual time=3334.445..3353.914 rows=1 loops=1) |
| -> Gather (cost=144310.28..144310.49 rows=2 width=8) (actual time=3326.780..3353.899 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=143310.28..143310.29 rows=1 width=8) (actual time=3310.060..3310.062 rows=1 loops=3) |
| -> Parallel Seq Scan on testindex (cost=0.00..143310.22 rows=24 width=0) (actual time=3310.056..3310.057 rows=0 loops=3) |
| Filter: (productname = 'flavor halibut'::text) |
| Rows Removed by Filter: 3724110 |
| Planning Time: 0.334 ms |
| Execution Time: 3353.991 ms |
We are doing a pattern matching search to return all the productname that start with fla.
%%sql
EXPLAIN ANALYZE SELECT COUNT(*) FROM fakedata.testindex WHERE productname LIKE 'fla%';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
10 rows affected.
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=144311.60..144311.61 rows=1 width=8) (actual time=1700.229..1710.236 rows=1 loops=1) |
| -> Gather (cost=144311.39..144311.60 rows=2 width=8) (actual time=1699.359..1710.227 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=143311.39..143311.40 rows=1 width=8) (actual time=1693.468..1693.469 rows=1 loops=3) |
| -> Parallel Seq Scan on testindex (cost=0.00..143310.22 rows=465 width=0) (actual time=0.139..1691.540 rows=9271 loops=3) |
| Filter: (productname ~~ 'fla%'::text) |
| Rows Removed by Filter: 3714839 |
| Planning Time: 0.088 ms |
| Execution Time: 1710.270 ms |
Note
A query is turned into an internal execution plan breaking the query into specific elements that are re-ordered and optimized. Read more about EXPLAIN here. This blog also explains it well.
These are computers, so it’s faster than humans to go through all the pages, but it’s inefficient. Won’t it be cool to tell the database that the data you are looking for is only on certain pages, so database don’t want to go through all these pages !!! This is WHY we want indexes. We will see if indexing can speed up the previous query.
WHAT are indexes?#
Indexes make a map of each of these rows effectively as where they are put into the page files so we can do a sequential scan of an index to find the pointer to page/pages this information is stored. So the database can go straight to those pages and skip the rest of the pages.
Index gets applied to a column in a table. As we were querying on the column productname, let’s now apply an index to the column productname in the database and see how the query execution plan changes. Will it improve the speed? Let’s see..
%%sql
CREATE INDEX if not exists default_testindex_index ON fakedata.testindex (productname varchar_pattern_ops);
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
Done.
[]
%%sql
explain analyze select count(*) from fakedata.testindex where productname = 'flavor halibut';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
6 rows affected.
| QUERY PLAN |
|---|
| Aggregate (cost=5.59..5.60 rows=1 width=8) (actual time=0.035..0.035 rows=1 loops=1) |
| -> Index Only Scan using default_testindex_index on testindex (cost=0.43..5.45 rows=58 width=0) (actual time=0.031..0.031 rows=0 loops=1) |
| Index Cond: (productname = 'flavor halibut'::text) |
| Heap Fetches: 0 |
| Planning Time: 0.212 ms |
| Execution Time: 0.065 ms |
%%sql
EXPLAIN ANALYZE
SELECT COUNT(*) FROM fakedata.testindex
WHERE productname LIKE 'fla%';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
7 rows affected.
| QUERY PLAN |
|---|
| Aggregate (cost=7.25..7.26 rows=1 width=8) (actual time=5.837..5.838 rows=1 loops=1) |
| -> Index Only Scan using default_testindex_index on testindex (cost=0.43..4.46 rows=1117 width=0) (actual time=0.045..4.370 rows=27814 loops=1) |
| Index Cond: ((productname ~>=~ 'fla'::text) AND (productname ~<~ 'flb'::text)) |
| Filter: (productname ~~ 'fla%'::text) |
| Heap Fetches: 0 |
| Planning Time: 0.208 ms |
| Execution Time: 5.880 ms |
Hurrayyy!!!! It increased the speed, and you see that your query planner used the index to speed up queries.
But keep in mind if the selection criteria are too broad, or the INDEX is too imprecise, the query planner will skip it.
Let’s try that out.
%%sql
EXPLAIN ANALYZE
SELECT COUNT(*) FROM fakedata.testindex
WHERE productname LIKE '%';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
9 rows affected.
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=155947.12..155947.13 rows=1 width=8) (actual time=2240.885..2247.992 rows=1 loops=1) |
| -> Gather (cost=155946.90..155947.11 rows=2 width=8) (actual time=2240.513..2247.982 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=154946.90..154946.91 rows=1 width=8) (actual time=2232.934..2232.935 rows=1 loops=3) |
| -> Parallel Seq Scan on testindex (cost=0.00..143310.22 rows=4654672 width=0) (actual time=0.019..1680.630 rows=3724110 loops=3) |
| Filter: (productname ~~ '%'::text) |
| Planning Time: 0.082 ms |
| Execution Time: 2248.028 ms |
It’s good that database engines are smart enough to identify if it’s better to look at an index or perform a sequential scan. Here in this example, the database engine understands the query is too broad. But, ultimately, it’s going to search through entire rows, so it’s better to perform a sequential scan rather than look up at the index.
As the index is making a map in the disk, it takes up disk storage. Let’s see how much space this index is taking up
%%sql
SELECT pg_size_pretty (pg_indexes_size('fakedata.testindex'));
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
1 rows affected.
| pg_size_pretty |
|---|
| 83 MB |
Now we realize indexes are great! So let’s try some different queries.
%%sql
EXPLAIN ANALYZE
SELECT COUNT(*) FROM fakedata.testindex
WHERE productname LIKE '%fla%';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
10 rows affected.
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=144545.55..144545.56 rows=1 width=8) (actual time=1697.465..1704.312 rows=1 loops=1) |
| -> Gather (cost=144545.33..144545.54 rows=2 width=8) (actual time=1697.112..1704.302 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=143545.33..143545.34 rows=1 width=8) (actual time=1689.651..1689.652 rows=1 loops=3) |
| -> Parallel Seq Scan on testindex (cost=0.00..143310.22 rows=94043 width=0) (actual time=0.078..1685.605 rows=22257 loops=3) |
| Filter: (productname ~~ '%fla%'::text) |
| Rows Removed by Filter: 3701853 |
| Planning Time: 0.084 ms |
| Execution Time: 1704.355 ms |
This was not too broad search; we were trying to return all the elements that contains fla. But why didn’t it speed things up? So we can’t always go with default indexing, and it might not be helpful in all cases. That’s why it’s important to know about different indexes and a general understanding of how it works. This will help you to choose indexes properly in various situations.
Different kinds of indexes#
In this section, we will go through various kinds of indexes and syntax for creating them;
B-Tree (binary search tree - btree)
Hash
GIST (Generalized Search Tree)
GIN (Generalized Inverted Tree)
BRIN (Block Range Index)
Each has its own set of operations, tied to Postgres functions/operators. For example, you can read about indexes here.
The general syntax for making an index:
CREATE INDEX indexname ON schema.tablename USING method (columnname opclass);
B-Tree#
B-Tree is the default index, and you can see we used this previously for column productname.
General Syntax: Here the Operator_Classes is optional. Read more on it here
CREATE INDEX if not exists index_name ON schema.tablename (columnname Operator_Classes);
Example:
CREATE INDEX if not exists default_testindex_index ON fakedata.testindex (productname varchar_pattern_ops);
Note
If you are using B-Tree indexing on a numeric column that you don’t want to specify ‘Operator_Classes’. Operator class varchar_pattern_ops is used for character column.
You will do it on a numeric column in worksheet 3.
Values are balanced across nodes to produce a minimum-spanning tree.
Each node splits values, each “leaf” points to addresses.
All nodes are equidistant from the root.
High Key
Btree is not just about dealing with numbers but also handling text columns; that’s the reason why I gave varchar_pattern_ops as opclass. So in this example, finds the field that we are looking for in just 3 steps, rather than scanning 7 rows.
Hash#
Cell values are hashed (encoded) form and mapped to address “buckets”
Hash -> bucket mappings -> disk address
The hash function tries to balance the number of buckets & number of addresses within a bucket
Hash only supports EQUALITY
Let’s create a hash index on column productname and execute queries that we ran before
%%sql
DROP INDEX fakedata.default_testindex_index;
CREATE INDEX if not exists hash_testindex_index ON fakedata.testindex USING hash(productname);
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
Done.
Done.
[]
%%sql
explain analyze select count(*) from fakedata.testindex where productname = 'flavor halibut';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
7 rows affected.
| QUERY PLAN |
|---|
| Aggregate (cost=232.78..232.79 rows=1 width=8) (actual time=0.014..0.014 rows=1 loops=1) |
| -> Bitmap Heap Scan on testindex (cost=4.45..232.63 rows=58 width=0) (actual time=0.011..0.011 rows=0 loops=1) |
| Recheck Cond: (productname = 'flavor halibut'::text) |
| -> Bitmap Index Scan on hash_testindex_index (cost=0.00..4.43 rows=58 width=0) (actual time=0.010..0.010 rows=0 loops=1) |
| Index Cond: (productname = 'flavor halibut'::text) |
| Planning Time: 0.109 ms |
| Execution Time: 0.038 ms |
%%sql
EXPLAIN ANALYZE
SELECT COUNT(*) FROM fakedata.testindex
WHERE productname LIKE 'fla%';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
10 rows affected.
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=144311.60..144311.61 rows=1 width=8) (actual time=1178.042..1184.899 rows=1 loops=1) |
| -> Gather (cost=144311.39..144311.60 rows=2 width=8) (actual time=1178.032..1184.891 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=143311.39..143311.40 rows=1 width=8) (actual time=1172.461..1172.462 rows=1 loops=3) |
| -> Parallel Seq Scan on testindex (cost=0.00..143310.22 rows=465 width=0) (actual time=0.053..1170.953 rows=9271 loops=3) |
| Filter: (productname ~~ 'fla%'::text) |
| Rows Removed by Filter: 3714839 |
| Planning Time: 0.092 ms |
| Execution Time: 1184.934 ms |
%%sql
SELECT pg_size_pretty (pg_indexes_size('fakedata.testindex'));
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
1 rows affected.
| pg_size_pretty |
|---|
| 381 MB |
Why do you think hash takes up more space here?
When do you think it’s best to you hash indexing?
GIN#
Before talking about GIN indexes, let me give you a little bit of background on a full-text search. Full-text search is usually used if you have a column with sentences and you need to query rows based on the match for a particular string in that sentence. For example, say we want to get rows with new in the sentence column.
row number |
sentence |
|---|---|
1 |
this column has to do some thing with new year, also has to do something with row |
2 |
this column has to do some thing with colors |
3 |
new year celebrated on 1st Jan |
4 |
new year celebration was very great this year |
5 |
This is about cars released this year |
We usually query it this way, this definitely will return our result, but it takes time.
%%sql
SELECT * FROM fakedata.testindex
WHERE productname LIKE '%new%';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
27510 rows affected.
| nameid | productid | productname | price |
|---|---|---|---|
| 89802 | 31504 | Italy newsstand | $66.23 |
| 89802 | 129142 | drizzle newsprint | $76.29 |
| 89857 | 35008 | news fortnight | $34.05 |
| 89939 | 129142 | drizzle newsprint | $76.29 |
| 90009 | 80002 | tray news | $8.63 |
| 90031 | 198374 | newsprint pond | $50.85 |
| 90046 | 129142 | drizzle newsprint | $76.29 |
| 90058 | 129142 | drizzle newsprint | $76.29 |
| 90089 | 31504 | Italy newsstand | $66.23 |
| 90192 | 30475 | news pheasant | $58.80 |
| 90252 | 93065 | criminal newsstand | $5.81 |
| 90300 | 113322 | perfume newsprint | $90.00 |
| 90443 | 30475 | news pheasant | $58.80 |
| 90455 | 145121 | newsprint place | $48.11 |
| 90474 | 129142 | drizzle newsprint | $76.29 |
| 90490 | 231226 | carpenter newsstand | $71.82 |
| 90509 | 192628 | hemp news | $48.81 |
| 90616 | 126880 | newsstand chard | $41.71 |
| 90695 | 126880 | newsstand chard | $41.71 |
| 90712 | 115033 | news beautician | $67.54 |
That’s why we go for full-text search; I found this blog to help understand this.
SELECT * FROM fakedata.testindex
WHERE to_tsvector('english', sentence) @@ to_tsquery('english','new');'
Here we are converting the type of column sentence to tsvector
Here the first row in sentence column this column has to do something with the new year, also has to do something with row will be represented like this internally
'also':11 'column':2 'do':5,14 'has':3,12 'new':9 'row':17 'some':6 'something':15 'thing':7 'this':1 'to':4,13 'with':8,16 'year':10
to_tsquery is how you query, and here we query for new using to_tsquery('english','new').
Postgres does a pretty good job with the full-text search, but if we want to speed up the search, we go for GIN indexes. The column needs to be of tsvector type for a full-text search.
Indexing many values to the same row
Inverse of B-tree - one value to many rows e.g., “quick”, or “brown” or “the” all point to row 1
Most commonly used for
full-text searching.
Let’s try these on our tables; Following is how much time it will take without indexing.
%%sql
EXPLAIN ANALYZE SELECT count(*) FROM fakedata.testindex WHERE to_tsvector('english', productname) @@ to_tsquery('english','flavor');
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
10 rows affected.
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=1308153.11..1308153.12 rows=1 width=8) (actual time=29598.231..29601.045 rows=1 loops=1) |
| -> Gather (cost=1308152.89..1308153.10 rows=2 width=8) (actual time=29593.871..29601.033 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=1307152.89..1307152.90 rows=1 width=8) (actual time=29582.633..29582.634 rows=1 loops=3) |
| -> Parallel Seq Scan on testindex (cost=0.00..1307094.70 rows=23276 width=0) (actual time=18.318..29578.335 rows=3158 loops=3) |
| Filter: (to_tsvector('english'::regconfig, productname) @@ '''flavor'''::tsquery) |
| Rows Removed by Filter: 3720952 |
| Planning Time: 6.980 ms |
| Execution Time: 29601.098 ms |
Let’s see how much time it is going to take with indexing. Before that we want to create index for the column.
%%time
%%sql
DROP INDEX if exists fakedata.hash_testindex_index;
DROP INDEX if exists fakedata.pgweb_idx;
CREATE INDEX if not exists pgweb_idx ON fakedata.testindex USING GIN (to_tsvector('english', productname));
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
Done.
Done.
Done.
CPU times: user 24.5 ms, sys: 16.5 ms, total: 40.9 ms
Wall time: 52.3 s
Here is how much time it is taking with indexing.
%%sql
EXPLAIN ANALYZE SELECT count(*) FROM fakedata.testindex WHERE to_tsvector('english', productname) @@ to_tsquery('english','flavor');
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
12 rows affected.
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=87233.51..87233.52 rows=1 width=8) (actual time=13.199..14.678 rows=1 loops=1) |
| -> Gather (cost=87233.30..87233.51 rows=2 width=8) (actual time=13.182..14.669 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=86233.30..86233.31 rows=1 width=8) (actual time=5.809..5.810 rows=1 loops=3) |
| -> Parallel Bitmap Heap Scan on testindex (cost=520.93..86175.11 rows=23276 width=0) (actual time=2.160..5.444 rows=3158 loops=3) |
| Recheck Cond: (to_tsvector('english'::regconfig, productname) @@ '''flavor'''::tsquery) |
| Heap Blocks: exact=1156 |
| -> Bitmap Index Scan on pgweb_idx (cost=0.00..506.96 rows=55862 width=0) (actual time=4.318..4.319 rows=9474 loops=1) |
| Index Cond: (to_tsvector('english'::regconfig, productname) @@ '''flavor'''::tsquery) |
| Planning Time: 0.198 ms |
| Execution Time: 14.723 ms |
Wooohoooo!!! A great improvement!! By creating a materialized view (We will see more about views in next class) with a computed tsvector column, we can make searches even faster, since it will not be necessary to redo the to_tsvector calls to verify index matches.
%%sql
CREATE MATERIALIZED VIEW if not exists fakedata.testindex_materialized as select *,to_tsvector('english', productname) as productname_ts
FROM fakedata.testindex;
CREATE INDEX if not exists pgweb_idx_mat ON fakedata.testindex_materialized USING GIN (productname_ts);
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
Done.
Done.
[]
%%sql
EXPLAIN ANALYZE SELECT count(*) FROM fakedata.testindex_materialized WHERE productname_ts @@ to_tsquery('english','flavor');
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
8 rows affected.
| QUERY PLAN |
|---|
| Aggregate (cost=17094.34..17094.35 rows=1 width=8) (actual time=7.656..7.658 rows=1 loops=1) |
| -> Bitmap Heap Scan on testindex_materialized (cost=62.97..17081.77 rows=5028 width=0) (actual time=3.984..6.855 rows=9474 loops=1) |
| Recheck Cond: (productname_ts @@ '''flavor'''::tsquery) |
| Heap Blocks: exact=9160 |
| -> Bitmap Index Scan on pgweb_idx_mat (cost=0.00..61.71 rows=5028 width=0) (actual time=2.188..2.189 rows=9474 loops=1) |
| Index Cond: (productname_ts @@ '''flavor'''::tsquery) |
| Planning Time: 0.398 ms |
| Execution Time: 7.700 ms |
This indexing does speed up things if we want to search for a particular word from a column, like flavor. But if we want to search for some pattern within a sentence, then this index won’t help. EG the query what we trying from beginning
%%sql
EXPLAIN ANALYZE
SELECT COUNT(*) FROM fakedata.testindex
WHERE productname LIKE '%fla%';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
10 rows affected.
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=144545.55..144545.56 rows=1 width=8) (actual time=1585.257..1606.664 rows=1 loops=1) |
| -> Gather (cost=144545.33..144545.54 rows=2 width=8) (actual time=1584.929..1606.655 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=143545.33..143545.34 rows=1 width=8) (actual time=1572.141..1572.141 rows=1 loops=3) |
| -> Parallel Seq Scan on testindex (cost=0.00..143310.22 rows=94043 width=0) (actual time=0.062..1568.632 rows=22257 loops=3) |
| Filter: (productname ~~ '%fla%'::text) |
| Rows Removed by Filter: 3701853 |
| Planning Time: 0.081 ms |
| Execution Time: 1606.700 ms |
Hence, we want a different flavor of gin indexing, trigram index. I found this blog to help understand this.
Trigrams Details (OPTIONAL)#
Trigrams are a special case of N-grams. The concept relies on dividing the sentence into a sequence of three consecutive letters, and this result is considered as a set. Before performing this process
Two blank spaces are added at the beginning.
One at the end.
Double ones replace single spaces.
The trigram set corresponding to “flavor halibut” looks like this:
{” f”,” h”,” fl”,” ha”,ali,avo,but,fla,hal,ibu,lav,lib,”or “,”ut “,vor}
These are considered words, and the rest remains the same as what we discussed before. To use this index add gin_trgm_ops as operator class. Let’s do it.
%%sql
CREATE EXTENSION pg_trgm;
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
(psycopg2.errors.DuplicateObject) extension "pg_trgm" already exists
[SQL: CREATE EXTENSION pg_trgm;]
(Background on this error at: https://sqlalche.me/e/14/f405)
%%sql
DROP INDEX if exists fakedata.pgweb_idx;
CREATE INDEX pgweb_idx ON fakedata.testindex USING GIN (productname gin_trgm_ops);
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
Done.
Done.
[]
Let’s see if it speeds up the query that we tried in many cases before.
%%sql
EXPLAIN ANALYZE
SELECT COUNT(*) FROM fakedata.testindex
WHERE productname LIKE '%fla%';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
12 rows affected.
| QUERY PLAN |
|---|
| Finalize Aggregate (cost=124690.89..124690.90 rows=1 width=8) (actual time=132.111..138.470 rows=1 loops=1) |
| -> Gather (cost=124690.67..124690.88 rows=2 width=8) (actual time=131.608..138.461 rows=3 loops=1) |
| Workers Planned: 2 |
| Workers Launched: 2 |
| -> Partial Aggregate (cost=123690.67..123690.68 rows=1 width=8) (actual time=122.986..122.987 rows=1 loops=3) |
| -> Parallel Bitmap Heap Scan on testindex (cost=2093.20..123455.56 rows=94043 width=0) (actual time=33.997..120.030 rows=22257 loops=3) |
| Recheck Cond: (productname ~~ '%fla%'::text) |
| Heap Blocks: exact=21059 |
| -> Bitmap Index Scan on pgweb_idx (cost=0.00..2036.78 rows=225704 width=0) (actual time=27.706..27.706 rows=66772 loops=1) |
| Index Cond: (productname ~~ '%fla%'::text) |
| Planning Time: 0.161 ms |
| Execution Time: 138.513 ms |
%%sql
EXPLAIN ANALYZE
SELECT COUNT(*) FROM fakedata.testindex
WHERE productname LIKE '%flavor%';
* postgresql://postgres:***@database-2.ch0m6kus6nrp.us-east-1.rds.amazonaws.com:5432/postgres
8 rows affected.
| QUERY PLAN |
|---|
| Aggregate (cost=4333.15..4333.16 rows=1 width=8) (actual time=17.011..17.012 rows=1 loops=1) |
| -> Bitmap Heap Scan on testindex (cost=256.66..4330.36 rows=1117 width=0) (actual time=4.770..16.420 rows=9474 loops=1) |
| Recheck Cond: (productname ~~ '%flavor%'::text) |
| Heap Blocks: exact=8925 |
| -> Bitmap Index Scan on pgweb_idx (cost=0.00..256.38 rows=1117 width=0) (actual time=3.331..3.331 rows=9474 loops=1) |
| Index Cond: (productname ~~ '%flavor%'::text) |
| Planning Time: 0.099 ms |
| Execution Time: 17.051 ms |
Finally, we see this query is using indexes, and it did speed up the query.
GIST#
Supports many search types, including spatial and full text.
Can be extended to custom data types
Balanced tree-based method (nodes & leaves)
BRIN#
Indexes block statistics for ordered data
Block min & max mapped to index
Search finds block first, then finds values
Best for larger volumes of data
This is essentially how we read a book’s index.
Usage
CREATE INDEX index_name ON schema.table/view USING BRIN(columnname);
We will use this later when we go through our Twitter example (tomorrow’s lecture).
Now we have learned about indexes, can you answer these questions?
What are indexes?
Different types of indexes?
When to use indexes?
How to use an index?
Why do I need an index?
Compared to Other Services (optional)#
BigQuery, AWS RedShift
Don’t use Indexes, infrastructure searches whole “columns”
MongoDB
Single & multiparameter indexes (similar to b-tree)
spatial (similar to GIST)
text indexes (similar to GIN)
hash index
Neo4j (Graph Database)
b-tree and full text
Summary#
Indexes are important to speed up operations
Indexes are optimized to certain kinds of data
Index performance can be assessed using
EXPLAIN.Indexes can come at a cost and should be careful in selecting it.
Class activity#
Note
Check worksheet 3 in canvas for detailed instructions on activity and submission instructions.
Setting up index
Experience the performance with/without and with different types of indexes.