Index best practice and hypothetical indexes in PostgreSQL

Transcript

1. Introduction Indexes in PostgreSQL Tips and caveat Index best practice and hypothetical indexes in PostgreSQL Julien Rouhaud COSCUP 2026, Taipei, Taiwan Aug. 9th 2026 1/34 Julien Rouhaud Index best practice
2. Introduction Indexes in PostgreSQL Tips and caveat Who am I Julien Rouhaud Worked with PostgreSQL since 2008 PostgreSQL Major Contributor, developer and DBA Author of HypoPG, PoWA and other tools Lead Founding Engineer at Nile 2/34 Julien Rouhaud Index best practice
3. Introduction Indexes in PostgreSQL Tips and caveat Agenda 1 Introduction 2 Indexes in PostgreSQL 3 Tips and caveat 3/34 Julien Rouhaud Index best practice
4. Introduction Indexes in PostgreSQL Tips and caveat 1 Introduction 2 Indexes in PostgreSQL 3 Tips and caveat Before we get started 4/34 Julien Rouhaud Index best practice
5. Introduction Indexes in PostgreSQL Tips and caveat Before we get started What is this talk about Overview of what are the index possibilities in postgres not a deep dive in all various index types 5/34 Julien Rouhaud Index best practice
6. Introduction Indexes in PostgreSQL Tips and caveat Before we get started Few reminders index are not part of the standard index != constraint indexes should target a query, or a pattern indexes are not a magical solution for all performance problems EXPLAIN is your friend (resources) 6/34 Julien Rouhaud Index best practice
7. Introduction Indexes in PostgreSQL Tips and caveat 1 Introduction 2 Indexes in PostgreSQL 3 Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features 7/34 Julien Rouhaud Index best practice
8. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features Lots of kind of indexes Postgres has lots of different index method (AM or IAM) each can support different features (like unicity, index-only scan...) each has different use cases not every datatype and/or operator can be indexed by any access method 8/34 Julien Rouhaud Index best practice
9. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features What index can be used with a datatype ? =# SELECT DISTINCT am.amname, amproclefttype::regtype::text, amprocrighttype::regtype::text FROM pg_am am JOIN pg_opfamily f ON f.opfmethod = am.oid JOIN pg_opclass c ON c.opcfamily = f.oid JOIN pg_amproc p ON p.amprocfamily = f.oid WHERE p.amproclefttype = 'int4'::regtype; amname | amproclefttype | amprocrighttype --------+----------------+----------------btree | integer | integer btree | integer | smallint hash | integer | integer brin | integer | integer btree | integer | bigint (5 rows) 9/34 Julien Rouhaud Index best practice
10. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features What index can be used with an operator ? =# SELECT DISTINCT amname, o.oprname FROM pg_am am JOIN pg_opfamily f ON f.opfmethod = am.oid JOIN pg_amop op ON op.amopfamily = f.oid JOIN pg_operator o ON o.oid = op.amopopr WHERE o.oprname = '&&'; -- overlap amname | oprname --------+--------brin | && gin | && gist | && spgist | && (4 rows) 10/34 Julien Rouhaud Index best practice
11. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features Indexes are extensible operator class, which can be set at each column level CREATE INDEX ON tbl USING amname (column opclass_name, ...) some shipped by default you can add your custom processing to existing indexes e.g. btree_gist or btree_gin contrib extensions you can add also your own index implementation e.g. pg_vector extension 11/34 Julien Rouhaud Index best practice
12. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features btree the default AM, good for many use cases support most features (like unicity, OID, sort...), performant 12/34 Julien Rouhaud Index best practice
13. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features hash only supports = operator smaller and a bit faster than btree, but way more limited can be useful for very big attributes btree cannot index value bigger than 2.5kB (1/3 of a page) 13/34 Julien Rouhaud Index best practice
14. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features gin 1/2 Generalized INverted Index designed for composite values very good for low cardinality column a bit similar to bitmap indexes on some RBDMS can be slow to update but very fast on reads 14/34 Julien Rouhaud Index best practice
15. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features gin 2/2 Full Text Search (to_tsvector) Many operactor classes LIKE ’prefix%’ : text_pattern_ops LIKE ’%pattern%’ : gin_trgm_ops (pg_trgm extension) 15/34 Julien Rouhaud Index best practice
16. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features gist Generalized Search Tree template to implement various approach (B-tree, R-tree...) much faster to update than gin postgis is the main user but also exclusion constraint CREATE EXTENSION btree_gist; CREATE TABLE reservation ( room integer NOT NULL, during tstzrange NOT NULL, EXCLUDE USING gist (room WITH =, during WITH &&) ); also spgist (Space Partitioned GiST, rarely used) 16/34 Julien Rouhaud Index best practice
17. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features brin Block Range INdex summarized index, only store one set of metadata for many rows not very efficient, but very small designed for very big dataset that are ordered 17/34 Julien Rouhaud Index best practice
18. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features bloom bloom filters as an index only support equality can be useful if you have a table with a lot of attributes and you filter on multiple subsets of the columns 18/34 Julien Rouhaud Index best practice
19. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features Partial indexes indexes can have an associated predicate more specialized index, smaller and faster CREATE INDEX ON orders (client_id WHERE status = 'pending'); 19/34 Julien Rouhaud Index best practice
20. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features Functional indexes indexes can index the result of a (immutable) function or other (immutable) expression useful to overcome some limitations CREATE INDEX ON tbl ((md5(very_long_column))); or to avoid executing the function if it’s expensive you will also get statistics on it (same as CREATE STATISTICS) 20/34 Julien Rouhaud Index best practice
21. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features Index Only Scan or IOS indexes don’t have visibility option checking visibility in the heap is expensive if the target block is known as all-visible, the visibility check is not necessary relies on frequent enough VACUUM, hard to rely on 21/34 Julien Rouhaud Index best practice
22. Introduction Indexes in PostgreSQL Tips and caveat Lots of kind of indexes Index Access Methods and use cases Extra index features Covering indexes Add extra column(s) in the index for IOS CREATE INDEX ON customer(id) INCLUDE (name); SELECT name FROM customer WHERE id = 42; 22/34 Julien Rouhaud Index best practice
23. Introduction Indexes in PostgreSQL Tips and caveat 1 Introduction 2 Indexes in PostgreSQL 3 Tips and caveat Cost of Indexes 23/34 Julien Rouhaud Index best practice
24. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Indexes aren’t free Don’t create too many indexes Each index usually needs to be modified when a tuple is updated Heap-Only Tuple (HOT) optimisation : only possible if you modify non-indexes columns 24/34 Julien Rouhaud Index best practice
25. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Indexes aren’t free Column order usually matter (not for bloom at least) leading subset of columns only CREATE INDEX ON tbl USING btree (id, val); SELECT * FROM tbl WHERE id = 42; -- yes SELECT * FROM tbl WHERE val = 'value' AND id = 42; -- yes SELECT * FROM tbl WHERE val = 'value'; -- NO 25/34 Julien Rouhaud Index best practice
26. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Indexes are hard to remove [1/2] What indexes can be removed ? not indexes associated to constraints what about UNIQUE indexes ? How to reliably know that an index isn’t used anymore ? can use pg_stat_user_indexes.last_idx_scan ? what about time sensitive queries that run only once a week, month or year ? what about indexes used on a phyisical replication standby ? 26/34 Julien Rouhaud Index best practice
27. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Indexes are hard to remove [2/2] redundant indexes are usually safe to remove CREATE INDEX ON tbl USING btree (id); -- can likely be removed CREATE INDEX ON tbl USING btree (id, val); Check behavior if an index didn’t exist hypopg can hide indexes (EXPLAIN only) plantuner can hide them at execution time 27/34 Julien Rouhaud Index best practice
28. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes pgcluu Report on redundant indexes (source in function dump_redundantindexes) 28/34 Julien Rouhaud Index best practice
29. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Indexes don’t solve everything Indexes won’t help bad query or bad schema =# CREATE TABLE t(id integer PRIMARY KEY); =# INSERT INTO t SELECT generate_series(1, 10000); =# VACUUM ANALYZE t; =# PREPARE p(numeric) AS SELECT * FROM t WHERE id = $1; =# EXPLAIN (ANALYZE, COSTS OFF) EXECUTE p(1); QUERY PLAN --------------------------------------------------------Seq Scan on t (actual time=0.009..0.980 rows=1 loops=1) Filter: ((id)::numeric = '1'::numeric) Rows Removed by Filter: 9999 Planning Time: 0.238 ms Execution Time: 0.989 ms (5 rows) 29/34 Julien Rouhaud Index best practice
30. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Would postgres use my new index ? 1/3 extension hypopg can help let you declare virtual / hypothetical indexes not really created, instant and no resource wasted can be used in EXPLAIN command 30/34 Julien Rouhaud Index best practice
31. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Would postgres use my new index ? 2/3 Easy missing index scenario =# CREATE TABLE t2(id integer); CREATE TABLE =# INSERT INTO t2 SELECT generate_series(1, 10000); INSERT 0 10000 =# VACUUM ANALYZE t2; VACUUM =# EXPLAIN (COSTS OFF) SELECT * FROM t2 WHERE id = 1; QUERY PLAN -------------------Seq Scan on t2 Filter: (id = 1) (2 rows) 31/34 Julien Rouhaud Index best practice
32. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Would postgres use my new index ? 3/3 hypopg usage =# CREATE EXTENSION hypopg ; CREATE EXTENSION =# SELECT hypopg_create_index('CREATE INDEX ON t2(id)'); hypopg_create_index ---------------------------(13618,<13618>btree_t2_id) (1 row) =# EXPLAIN (COSTS OFF) SELECT * FROM t2 WHERE id = 1; QUERY PLAN -------------------------------------------------Index Only Scan using "<13618>btree_t2_id" on t2 Index Cond: (id = 1) (2 rows) 32/34 Julien Rouhaud Index best practice
33. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Missing index detection Many tools, different approach, most rely on hypopg PoWA, optimize the whole workload on a specific interval, aiming for less indexes but more useful pg_qualstats, like powa but self contained and in-memory dexter, brute force approach : create all possible hypothetical indexes on relevant tables and see which ones are used, including multi0column probably a lot more, open source or commercial . . . 33/34 Julien Rouhaud Index best practice
34. Introduction Indexes in PostgreSQL Tips and caveat Cost of Indexes Questions ? Blog: rjuju.github.io Š@rjuju123 ïrjuju 34/34 Julien Rouhaud Index best practice