https://github.com/supabase/index_advisor Skip to content Toggle navigation Sign in * Product + Actions Automate any workflow + Packages Host and manage packages + Security Find and fix vulnerabilities + Codespaces Instant dev environments + Copilot Write better code with AI + Code review Manage code changes + Issues Plan and track work + Discussions Collaborate outside of code Explore + All features + Documentation + GitHub Skills + Blog * Solutions For + Enterprise + Teams + Startups + Education By Solution + CI/CD & Automation + DevOps + DevSecOps Resources + Learning Pathways + White papers, Ebooks, Webinars + Customer Stories + Partners * Open Source + GitHub Sponsors Fund open source developers + The ReadME Project GitHub community articles Repositories + Topics + Trending + Collections * Pricing Search or jump to... Search code, repositories, users, issues, pull requests... Search [ ] Clear Search syntax tips Provide feedback We read every piece of feedback, and take your input very seriously. [ ] [ ] Include my email address so I can be contacted Cancel Submit feedback Saved searches Use saved searches to filter your results more quickly Name [ ] Query [ ] To see all available qualifiers, see our documentation. Cancel Create saved search Sign in Sign up You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session. You switched accounts on another tab or window. Reload to refresh your session. Dismiss alert {{ message }} supabase / index_advisor Public * * Notifications * Fork 1 * Star 577 * PostgreSQL Index Advisor supabase.com/docs/guides/database/extensions/index_advisor License PostgreSQL license 577 stars 1 fork Branches Tags Activity Star Notifications * Code * Issues 0 * Pull requests 0 * Actions * Projects 0 * Security * Insights Additional navigation options * Code * Issues * Pull requests * Actions * Projects * Security * Insights supabase/index_advisor This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. main BranchesTags Go to file Code Folders and files Last Last Name Name commit commit message date Latest commit History 38 Commits .github .github docs/img docs/img test test .gitignore .gitignore .pre-commit-config.yaml .pre-commit-config.yaml LICENSE LICENSE Makefile Makefile README.md README.md index_advisor--0.1.0--0.1.1.sql index_advisor--0.1.0--0.1.1.sql index_advisor--0.1.0.sql index_advisor--0.1.0.sql index_advisor--0.1.1.sql index_advisor--0.1.1.sql index_advisor--0.1.2.sql index_advisor--0.1.2.sql index_advisor--0.2.0.sql index_advisor--0.2.0.sql index_advisor.control index_advisor.control View all files Repository files navigation * README * Code of conduct * PostgreSQL license PostgreSQL Index Advisor PostgreSQL version License tests A PostgreSQL extension for recommending indexes to improve query performance. Dashboard Features * Supports generic parameters e.g. $1, $2 * Supports materialized views * Identifies tables/columns obfuscaed by views API Description For a given query, searches for a set of SQL DDL create index statements that improve the query's execution time; Signature index_advisor(query text) returns table ( startup_cost_before jsonb, startup_cost_after jsonb, total_cost_before jsonb, total_cost_after jsonb, index_statements text[], errors text[] ) Usage For a minimal example, the index_advisor function can be given a single table query with a filter on an unindexed column. create extension if not exists index_advisor cascade; create table book( id int primary key, title text not null ); select * from index_advisor('select book.id from book where title = $1'); startup_cost_before | startup_cost_after | total_cost_before | total_cost_after | index_statements | errors ---------------------+--------------------+-------------------+------------------+-----------------------------------------------------+-------- 0.00 | 1.17 | 25.88 | 6.40 | {"CREATE INDEX ON public.book USING btree (title)"},| {} (1 row) More complex queries may generate additional suggested indexes create extension if not exists index_advisor cascade; create table author( id serial primary key, name text not null ); create table publisher( id serial primary key, name text not null, corporate_address text ); create table book( id serial primary key, author_id int not null references author(id), publisher_id int not null references publisher(id), title text ); create table review( id serial primary key, book_id int references book(id), body text not null ); select * from index_advisor(' select book.id, book.title, publisher.name as publisher_name, author.name as author_name, review.body review_body from book join publisher on book.publisher_id = publisher.id join author on book.author_id = author.id join review on book.id = review.book_id where author.id = $1 and publisher.id = $2 '); startup_cost_before | startup_cost_after | total_cost_before | total_cost_after | index_statements | errors ---------------------+--------------------+-------------------+------------------+-----------------------------------------------------------+-------- 27.26 | 12.77 | 68.48 | 42.37 | {"CREATE INDEX ON public.book USING btree (author_id)", | {} "CREATE INDEX ON public.book USING btree (publisher_id)", "CREATE INDEX ON public.review USING btree (book_id)"} (3 rows) Install Requires Postgres with hypopg installed. git clone https://github.com/supabase/index_advisor.git cd index_advisor sudo make install Run Tests make install; make installcheck About PostgreSQL Index Advisor supabase.com/docs/guides/database/extensions/index_advisor Topics postgres extension postgresql indexing Resources Readme License PostgreSQL license Code of conduct Code of conduct Activity Custom properties Stars 577 stars Watchers 2 watching Forks 1 fork Report repository Releases 1 v0.2.0 Latest Apr 3, 2024 Sponsor this project Sponsor Learn more about GitHub Sponsors Packages 0 No packages published Contributors 2 * @olirice olirice Oliver Rice * @kiwicopple kiwicopple Copple Languages * PLpgSQL 99.2% * Makefile 0.8% Footer (c) 2024 GitHub, Inc. Footer navigation * Terms * Privacy * Security * Status * Docs * Contact * Manage cookies * Do not share my personal information You can't perform that action at this time.