Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Postgres Index Types (and where to find them) | Montreal PostgreSQL Meetup | Louise Grandjonc

Citus Data
February 12, 2020

Postgres Index Types (and where to find them) | Montreal PostgreSQL Meetup | Louise Grandjonc

As developers we use indexes a lot, some by explicitly asking our ORM, some because of primary keys, unique constraint… But indexes go further than the default btree. And by the way, what is a btree? Louise is sharing a great talk about index types that postgreSQL has:
btree, gin, gist, sp-gist, brin, hash. What is the difference between them? What data type are they most fit for? How can they help with the performance of your application? How can you create a new index with a different type than the btree in python? All of these questions will be answered during this talk!

Citus Data

February 12, 2020
Tweet

More Decks by Citus Data

Other Decks in Technology

Transcript

  1. @louisemeta
    Postgres index types
    (And where to find them)
    Louise Grandjonc
    Montreal Postgres Meetup

    View full-size slide

  2. @louisemeta
    About me
    Software engineer at Citus/Microsoft
    Previously lead python developer
    Postgres enthusiast
    PostgresWomen co-founder
    @louisemeta on twitter
    www.louisemeta.com
    [email protected]
    @louisemeta !2

    View full-size slide

  3. @louisemeta
    What we’re going to talk about
    1. What are indexes for?
    2. Creating indexes
    3. B-Tree
    4. GIN
    5. GiST
    6. Brin
    @louisemeta !3

    View full-size slide

  4. @louisemeta
    First things first: the crocodiles
    • 250k crocodiles
    • 100k birds
    • 400K appointments
    @louisemeta !4

    View full-size slide

  5. @louisemeta
    What are indexes for?

    View full-size slide

  6. @louisemeta
    Constraints
    Some constraints transform into indexes.
    - PRIMARY KEY
    - UNIQUE
    - EXCLUDE USING
    "crocodile_pkey" PRIMARY KEY, btree (id)
    "crocodile_email_uq" UNIQUE CONSTRAINT, btree (email)
    Indexes:
    "appointment_pkey" PRIMARY KEY, btree (id)
    "appointment_crocodile_id_schedule_excl" EXCLUDE USING gist
    (crocodile_id WITH =, schedule WITH &&)
    In the crocodile table
    In the appointment table
    @louisemeta !6

    View full-size slide

  7. @louisemeta
    Query optimization
    Often the main reason why we create indexes
    Why do indexes make queries faster
    In an index, tuples (value, pointer) are stored.
    Instead of reading the entire table for a value, you just go to the index (kind of like in an
    encyclopedia)
    @louisemeta !7

    View full-size slide

  8. @louisemeta
    Creating indexes
    @louisemeta

    View full-size slide

  9. @louisemeta
    Creating an index
    @louisemeta !9
    Let’s say we would like to do queries like:
    SELECT * FROM crocodile WHERE number_of_teeth = 10;
    Time: 31ms
    Here is how to create a simple index:
    CREATE INDEX (optional index name) ON crocodile (number_of_teeth);
    SELECT * FROM crocodile WHERE number_of_teeth = 10;
    Time: 6ms
    New timing

    View full-size slide

  10. @louisemeta
    Creating a unique index
    @louisemeta !10
    We want to make sure that you can’t create two account with
    the same email:
    CREATE UNIQUE INDEX ON crocodile (email);
    INSERT INTO crocodile (email, first_name, last_name,
    birthday, number_of_teeth) VALUES ('[email protected]',
    'Louise', 'Grandjonc', '1991-12-21', 32);
    INSERT 0 1
    If I run the same insert again I get this error:
    DETAIL: Key (email)=([email protected]) already exists.

    View full-size slide

  11. @louisemeta
    Creating a partial index
    @louisemeta !11
    The table appointments has a boolean done indicating when an
    appointment has already been handled by a bird.
    95% of the appointments in our database have done=True.
    SELECT * FROM appointment WHERE emergency_level > 8 AND done = False;
    CREATE INDEX ON crocodile (emergency_level) WHERE done is False;
    Size on the index: 352 kB
    Time of the query: 3.639 ms
    Size on the index:13MB
    Time of the query: 29.106 ms
    Index without condition New index with condition

    View full-size slide

  12. @louisemeta
    Creating a partial unique index
    @louisemeta !12
    Want to add a UNIQUE index but have duplicates due to history or
    soft delete?
    CREATE UNIQUE INDEX ON crocodile (email)
    WHERE created_at > ‘2019-01-01’;
    Size of previous index: 6648 kB
    Size of the new index: 112 kB
    Why use a partial unique index?
    - Save disk space with smaller index
    - Faster inserts because the index tree is shorter to explore
    (especially when you have a huge volume of old data)

    View full-size slide

  13. @louisemeta
    Creating a multi-column index
    @louisemeta !13
    We have a job running regularly to list a bird’s emergencies, it runs
    the following query:
    SELECT * FROM appointment WHERE emergency_level >= 9 and
    plover_bird_id = 22551;
    Time: 41.560 ms
    CREATE INDEX ON appointment (plover_bird_id, emergency_level);
    Time after: 0.606 ms

    View full-size slide

  14. @louisemeta
    Ordering the columns in a multi-column index
    @louisemeta !14
    Two things to consider:
    - Re-using the index: the rightmost columns can be re-used for other queries.
    The first column will be ordered, so the index can be used by this query:
    SELECT * FROM appointment WHERE plover_bird_id = 22551;
    30 8
    55 10
    96 7
    31 5
    31 10
    55 10
    56 10
    57 3
    31 6 RowID
    31 10 RowID
    31 10 RowID
    32 1 RowID
    32 6 RowID

    55 10 RowID
    plover_bird_id
    em
    ergency_level

    View full-size slide

  15. @louisemeta
    Ordering the columns in a multi-column index
    @louisemeta !15
    - The most filtering columns should come first
    croco_talk=# SELECT COUNT(*) FROM appointment WHERE emergency_level
    >= 9;
    count
    -------
    75982
    (1 row)
    croco_talk=# SELECT COUNT(*) FROM appointment WHERE plover_bird_id =
    22551;
    count
    -------
    5
    (1 row)
    In this case, with plover_bird_id as the first column, it will first filter out and
    the second filter will be applied on only 5 rows.

    View full-size slide

  16. @louisemeta
    Indexes using INCLUDE
    @louisemeta !16
    New in Postgres 11
    For some cases, if you want to be able to have index only scans, and
    don’t have filter on a key:
    You could have an index
    SELECT SUM(fee) FROM appointment WHERE crocodile_id = 1;
    CREATE INDEX ON appointment (crocodile_id) INCLUDE (fee);
    Advantage over a multi-column index:
    - The values in INCLUDE are stored in the index, but not in the tree.
    - Less columns to index
    - Faster updates

    View full-size slide

  17. @louisemeta
    B-Trees
    @louisemeta

    View full-size slide

  18. @louisemeta
    B-Trees internal data structure
    @louisemeta !18
    Root
    High Key: None
    begin 16 31
    Parent
    High Key: 16
    begin 12 14
    Parent
    High Key: 31
    16 20
    Parent
    High Key: None
    31 33
    Leave
    High Key: 12
    Value: 1

    Pointer:
    croco 10
    Value: 1

    Pointer:
    croco 12
    Value : 2

    Pointer:
    croco 23
    … Value 10

    Pointer:
    croco 1
    Value: 11

    Pointer:
    croco 2
    Leave
    High Key: 14
    Value:
    12

    Pointer:
    croco 17
    Value:
    13

    Pointer:
    croco 3
    Value :
    13

    Pointer:
    croco 4
    … Value 13

    Pointer:
    croco 27

    Leave
    High Key: None
    Value: 33

    Pointer:
    croco 5
    Value: 33

    Pointer:
    croco 6
    Value : 33

    Pointer:
    croco 123
    … Value: 38

    Pointer:
    croco 26
    - A BTree in a balanced tree
    - All the leaves are at equal distance from the root.
    - A parent node can have multiple children minimizing the tree’s depth

    View full-size slide

  19. @louisemeta
    B-Trees internal data structure - 2
    Pages
    The root, the parents, and the leaves are all pages with the same structure.
    Pages have:
    - A block number (pointer)
    - A high key (defines the highest value found in a page)
    - Items
    @louisemeta !19

    View full-size slide

  20. @louisemeta
    B-Trees internal data structure - 4
    Pages high key
    - Any item in the page will have a value lower or equal to the high key
    And in page 575, there is no high key as it’s the
    rightmost page.
    In page 3, I will find crocodiles with 16 or less teeth
    In page 289, with 31 and less
    @louisemeta !20

    View full-size slide

  21. @louisemeta
    B-Trees internal data structure - 5
    Items
    An item contains:
    - A value (of the indexed row in the leaves, of the first row in the parents)
    - Pointer (to the row in the leaves, to the child page in the parents)
    @louisemeta !21

    View full-size slide

  22. @louisemeta
    To sum it up
    @louisemeta !22
    Root
    High Key: None
    begin 16 31
    Parent
    High Key: 16
    begin 12 14
    Parent
    High Key: 31
    16 20
    Parent
    High Key: None
    31 33
    Leave
    High Key: 12
    Value: 1

    Pointer:
    croco 10
    Value: 1

    Pointer:
    croco 12
    Value : 2

    Pointer:
    croco 23
    … Value 10

    Pointer:
    croco 1
    Value: 11

    Pointer:
    croco 2
    Leave
    High Key: 14
    Value:
    12

    Pointer:
    croco 17
    Value:
    13

    Pointer:
    croco 3
    Value :
    13

    Pointer:
    croco 4
    … Value 13

    Pointer:
    croco 27

    Leave
    High Key: None
    Value: 33

    Pointer:
    croco 5
    Value: 33

    Pointer:
    croco 6
    Value : 33

    Pointer:
    croco 123
    … Value: 38

    Pointer:
    croco 26
    - A Btree is a balanced tree
    - The values indexed are the values of the rows
    - Data is stored in pages
    - Pages have a high key defining the biggest value in the page
    - Pages have items pointing to an other page or the row.


    View full-size slide

  23. @louisemeta
    What are BTree good for?
    @louisemeta !23
    Root
    High Key: None
    begin 16 31
    Parent
    High Key: 16
    begin 12 14
    Parent
    High Key: 31
    16 20
    Parent
    High Key: None
    31 33
    Leave
    High Key: 12
    Value: 1

    Pointer:
    croco 10
    Value: 1

    Pointer:
    croco 12
    Value : 2

    Pointer:
    croco 23
    … Value 10

    Pointer:
    croco 1
    Value: 11

    Pointer:
    croco 2
    Leave
    High Key: 14
    Value:
    12

    Pointer:
    croco 17
    Value:
    13

    Pointer:
    croco 3
    Value :
    13

    Pointer:
    croco 4
    … Value 13

    Pointer:
    croco 27

    Leave
    High Key: None
    Value: 33

    Pointer:
    croco 5
    Value: 33

    Pointer:
    croco 6
    Value : 33

    Pointer:
    croco 123
    … Value: 38

    Pointer:
    croco 26
    BTrees are good for the following operations: =, >, <, >=, <=
    Why?
    Because the value indexed is the value of the column(s) so we can
    easily perform binary search in the BTree

    View full-size slide

  24. @louisemeta
    GIN

    View full-size slide

  25. @louisemeta
    GIN
    - Used to index arrays, jsonb, and tsvector (for fulltext search) columns.
    - Efficient for <@, &&, @@@ operators
    New column healed_teeth:
    croco=# SELECT email, number_of_teeth, healed_teeth FROM crocodile WHERE id =1;
    -[ RECORD 1 ]---+--------------------------------------------------------
    email | [email protected]
    number_of_teeth | 58
    healed_teeth | {16,11,55,27,22,41,38,2,5,40,52,57,28,50,10,15,1,12,46}
    !25

    View full-size slide

  26. @louisemeta
    Creating a GIN index
    Here is how to create the GIN index for this column
    CREATE INDEX ON crocodile USING GIN(healed_teeth);
    !26
    from django.contrib.postgres.indexes import GinIndex
    class Crocodile(models.Model):
    ...
    class Meta:
    indexes = [GinIndex(fields=['healed_teeth'])]
    Raw SQL

    View full-size slide

  27. @louisemeta
    GIN
    How is it different from a BTree?
    - In a GIN index, the array is split and each value is an entry
    - The values are unique
    - As the value is unique, in the leaves, we keep a list of pointers to the rows
    !27
    Root
    Value:
    Value: 10
    Value: 20

    Parent
    Value: 1
    Value: 4
    Value: 6

    Parent
    Value: 10
    Value: 15
    Value: 17

    Parent
    Value: 20
    Value: 24
    Value: 26

    Leaf
    Pointers: {(269, 49),
    (296, 51), (296, 54),
    (296, 57), …}
    Pointers: { (306, 33),
    (306, 35), (306,36), …}

    Leaf
    Pointer to posting tree

    Page
    Page
    Page
    Root
    Posting tree

    View full-size slide

  28. @louisemeta
    GIN
    How is it different from a BTree?
    Bitmap Heap Scan on crocodile
    (cost=516.59..6613.42 rows=54786 width=29)
    (actual time=15.960..38.197 rows=73275 loops=1)
    Recheck Cond: ('{1,2}'::integer[] <@ healed_teeth)
    Heap Blocks: exact=4218
    -> Bitmap Index Scan on crocodile_healed_teeth_idx
    (cost=0.00..502.90 rows=54786 width=0)
    (actual time=15.302..15.302 rows=73275 loops=1)
    Index Cond: ('{1,2}'::integer[] <@ healed_teeth)
    Planning time: 0.124 ms
    Execution time: 41.018 ms
    (7 rows)
    Seq Scan on crocodile (cost=…)
    Filter: ('{1,2}'::integer[] <@ healed_teeth)
    Rows Removed by Filter: 250728
    Planning time: 0.157 ms
    Execution time: 161.716 ms
    (5 rows)
    !28

    View full-size slide

  29. @louisemeta
    To sum it up
    @louisemeta !29
    - A GIN index is a balanced tree
    - Each value in the tree is unique
    - The row value is split and each value is an entry
    - Efficient for <@, &&, @@@ operators

    View full-size slide

  30. @louisemeta
    GIST

    View full-size slide

  31. @louisemeta
    GiST - keys
    Differences with a BTree index
    - Data isn’t ordered
    - The key ranges can overlap
    Which means that a same value can be inserted in different pages
    !31
    To be more readable, the following example here is for a Integer Range type :)
    Root
    Page block number: 0

    Page level: 0
    Value: [3, 5]
    Value: [0, 2]
    Value: [4, 8]
    Value: [7, 9]
    Parent
    Page block number: 4699

    Page level: 1
    Parent
    Page block number: 1610

    Page level: 1
    Parent
    Page block number: 813

    Page level: 1
    Parent
    Page block number: 6249

    Page level: 1

    View full-size slide

  32. @louisemeta
    Creating a GiST index
    !32
    Here is how to create the GiST index for this column
    CREATE INDEX ON appointment USING GIST(schedule);
    from django.contrib.postgres.indexes import GistIndex
    class Appointment(models.Model):
    ...
    class Meta:
    indexes = [GistIndex(fields=[‘schedule'])]
    Raw SQL

    View full-size slide

  33. @louisemeta
    Why use GiST
    - Useful for overlapping (geometries, array, range etc.)
    - Especially useful when using postgis
    - Nearest neighbor
    - Can be used for full text search (tsvector, tsquery)
    !33

    View full-size slide

  34. @louisemeta
    GiST or GIN for fulltext search
    movies=# CREATE INDEX ON film USING GIN(fulltext) with (fastupdate=off);
    CREATE INDEX
    Time: 8.083 ms
    movies=# INSERT INTO film (title, description, language_id) VALUES ('Nightmare at the
    dentist', 'A crocodile calls his dentist on halloween and ends up toothless and very
    sad, warning: not for kids, or teeth-sensitive crocodiles', 1);
    INSERT 0 1
    Time: 3.057 ms
    movies=# INSERT INTO film (title, description, language_id) VALUES ('Nightmare at the
    dentist', 'The terrible adventure of a crocodile who never goes to the dentist', 1);
    INSERT 0 1
    Time: 1.323 ms
    - Maintaining a GIN index is slower than GiST
    !34

    View full-size slide

  35. @louisemeta
    GiST or GIN for fulltext search
    - Lookups are faster with GIN
    movies=# SELECT COUNT(*) FROM film WHERE fulltext @@ to_tsquery('crocodile');
    count
    -------
    106
    (1 row)
    Time: 1.275 ms
    movies=# SELECT COUNT(*) FROM film WHERE fulltext @@ to_tsquery('crocodile');
    count
    -------
    106
    (1 row)
    Time: 0.467 ms
    !35

    View full-size slide

  36. @louisemeta
    GiST or GIN for fulltext search
    - GIN indexes are larger than GiST
    movies=# \di+ film_fulltext_idx
    List of relations
    Schema | Name | Type | Owner | Table | Size | Description
    --------+-------------------+-------+----------+-------+-------+-------------
    public | film_fulltext_idx | index | postgres | film | 88 kB |
    (1 row)
    movies=# \di+ film_fulltext_gin_idx
    List of relations
    Schema | Name | Type | Owner | Table | Size | Description
    --------+-----------------------+-------+----------+-------+--------+-------------
    public | film_fulltext_gin_idx | index | postgres | film | 112 kB |
    (1 row)
    !36

    View full-size slide

  37. @louisemeta
    BRIN

    View full-size slide

  38. @louisemeta
    BRIN
    Internal data structure
    - Block Range Index
    - Not a balanced tree
    - Not even a tree
    - Block range: group of pages physically adjacent
    - For each block range: the range of values is stored
    - BRIN indexes are very small
    - Fast scanning on large tables
    !38

    View full-size slide

  39. @louisemeta
    BRIN
    Internal data structure
    SELECT * FROM brin_page_items(get_raw_page('appointment_created_at_idx', 2), 'appointment_created_at_idx');
    itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value
    ------------+--------+--------+----------+----------+-------------+---------------------------------------------------
    1 | 0 | 1 | f | f | f | {2008-03-01 00:00:00-08 .. 2009-07-07 07:30:00-07}
    2 | 128 | 1 | f | f | f | {2009-07-07 08:00:00-07 .. 2010-11-12 15:30:00-08}
    3 | 256 | 1 | f | f | f | {2010-11-12 16:00:00-08 .. 2012-03-19 23:30:00-07}
    4 | 384 | 1 | f | f | f | {2012-03-20 00:00:00-07 .. 2013-07-26 07:30:00-07}
    5 | 512 | 1 | f | f | f | {2013-07-26 08:00:00-07 .. 2014-12-01 15:30:00-08}
    SELECT id, created_at FROM appointment WHERE ctid='(0, 1)'::tid;
    id | created_at
    --------+------------------------
    101375 | 2008-03-01 00:00:00-08
    (1 row)
    !39

    View full-size slide

  40. @louisemeta
    BRIN
    Internal data structure
    SELECT * FROM brin_page_items(get_raw_page('crocodile_birthday_idx', 2),
    'crocodile_birthday_idx');
    itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value
    ------------+--------+--------+----------+----------+-------------+----------------------------
    1 | 0 | 1 | f | f | f | {1948-09-05 .. 2018-09-04}
    2 | 128 | 1 | f | f | f | {1948-09-07 .. 2018-09-03}
    3 | 256 | 1 | f | f | f | {1948-09-05 .. 2018-09-03}
    4 | 384 | 1 | f | f | f | {1948-09-05 .. 2018-09-04}
    5 | 512 | 1 | f | f | f | {1948-09-05 .. 2018-09-02}
    6 | 640 | 1 | f | f | f | {1948-09-09 .. 2018-09-04}

    (14 rows)
    In this case, the values in birthday has no correlation with the physical
    location, the index would not speed up the search as all pages would have
    to be visited.
    BRIN is interesting for data where the value is correlated with the
    physical location.
    !40

    View full-size slide

  41. @louisemeta
    BRIN
    Warning on DELETE and INSERT
    SELECT * FROM brin_page_items(get_raw_page('appointment_created_at_idx', 2), 'appointment_created_at_idx');
    itemoffset | blknum | attnum | allnulls | hasnulls | placeholder | value
    ------------+--------+--------+----------+----------+-------------+---------------------------------------------------
    1 | 0 | 1 | f | f | f | {2008-03-01 00:00:00-08 .. 2018-07-01 07:30:00-07}
    2 | 128 | 1 | f | f | f | {2009-07-07 08:00:00-07 .. 2018-07-01 23:30:00-07}
    3 | 256 | 1 | f | f | f | {2010-11-12 16:00:00-08 .. 2012-03-19 23:30:00-07}
    4 | 384 | 1 | f | f | f | {2012-03-20 00:00:00-07 .. 2018-07-06 23:30:00-07}
    DELETE FROM appointment WHERE created_at >= '2009-07-07' AND created_at < ‘2009-07-08';
    DELETE FROM appointment WHERE created_at >= '2012-03-20' AND created_at < ‘2012-03-25';
    Deleted and then vacuum on the appointment table
    New rows are inserted in the free space after VACUUM
    BRIN index has some ranges with big data ranges.
    Search will visit a lot of pages.
    !41

    View full-size slide

  42. @louisemeta
    Creating a BRIN index
    !42
    Here is how to create the BRIN index for this column
    CREATE INDEX ON crocodile USING BRIN(created_at);
    from django.contrib.postgres.indexes import BrinIndeex
    class Crocodile(models.Model):
    ...
    class Meta:
    indexes = [BrinIndex(fields=['created_at'])]
    Raw SQL

    View full-size slide

  43. @louisemeta
    Conclusion
    - B-Tree
    - Great for <, >, =, >=, <=
    - GIN
    - Fulltext search, jsonb, arrays
    - Inserts can be slow because of unicity of the
    keys
    - GiST
    - Great for overlapping
    - Using key class functions
    - Can be implemented for any data type
    - BRIN
    - Great for huge table with correlation
    between value and physical location
    - <, >, =, >=, <=
    !43

    View full-size slide

  44. @louisemeta
    Questions
    Thanks for your attention
    Go read the articles www.louisemeta.com
    Now only the ones on BTrees and GIN are
    published, but I’ll announce the rest on
    twitter @louisemeta
    Crocodiles by https://www.instagram.com/zimmoriarty/?hl=en
    !44

    View full-size slide