9 ms·
Show HN: PostgreSQL index advisor
This is a Postgres extension that can determine if a query should have an index. For example, for this table:
create table book(
id int primary key,
title text not null
);
You can run `index_advisor()` to see if there should be an index on a select statement:
select *
from index_advisor('select book.id from book where title = $1');
And it will return (summarized):
{"CREATE INDEX ON public.book USING btree (title)"}
It works particularly well with pg_stat_statements[0] which tracks execution statistics of all SQL statements executed on your Postgres database.
It leans heavily on HypoPG[1], an excellent extension to determine if PostgreSQL will use a given index without spending resources to create them.
[0] pg_stat_statements: https://www.postgresql.org/docs/current/pgstatstatements.html https://www.postgresql.org/docs/current/pgstatstatements.htm...
[1] https://github.com/HypoPG/hypopg https://github.com/HypoPG/hypopg
- PikachuEXE 3y agoI am using https://github.com/ankane/pghero/ https://github.com/ankane/pghero/ and this is one of its features with GUI
- kiwicopple 3y agoAndrew builds some amazing tools for the postgres ecosystem (he is also the creator of pgvector)
- SiliconGuru 3y agoI need such a tool. The only concern is whether this advisor can really give reliable indexing advice? Is it based on cost or rules? Maybe I would dare to use it after knowing more about its implementation.
- deleted 3y ago[deleted]
- jitl 3y agoI’ve been needing this and also HypoPG, which I didn’t realize had been available on RDS for a year plus! The issue I’m struggling with these days is that I have an index that I want Postgres to use for one of the relations in a 3+ wide join, but unless I put a limit on a CTE of one of the tables, Postgres tries to run each join in parallel and ends up trying to join a bazillion rows like an asshole. These days wrangling the query planner has me on the verge of breaking up with pg :-(
- kiwicopple 3y agoi don't know how far it was developed, but I believe pg_plan_guarantee[0] was created to solve this sort of issue > This extension provides a mechanism, using which you can set your execution plan in stone; that is, Postgres will execute the plan you give it, or it will throw an error, but it will never try to guess a plan for you. [0] https://github.com/DrPostgres/pg_plan_guarantee https://github.com/DrPostgres/pg_plan_guarantee
- jitl 3y agoUgh I would love LOVE to have something like this or even better a stable API where I can manually specify my own physical query plan. As is, I’m on RDS and this thing is experimental so I have no way to use it.
- jaggederest 3y agoFor what it's worth, depending on how many people and/or budget you have available to man on call rotations, running your own postgresql server with replication is not that hard. The RDS premium is like a whole engineer's salary by the time you get to Large Databases (tm), and if you're running production, staging, and maybe a data warehouse, it starts to rapidly be able to pay for a whole team.
- jitl 3y agoPeople aka eng time is our scarcest resource. Luckily the thing I’m struggling with is nice-to-have, not critical, will try pg_hint_plan as suggested, I didn’t realize it’s available on RDS. Otherwise will abandon.
- jaggederest 3y agoAh you made me look, it's a shame you're only hiring in SF, I love these kinds of problems. Cheers!
- jaggederest 3y ago
- itsgrimetime 3y agoDoes something like this for MongoDB? Searching hasn’t turned up anything useful.
- garyal 3y agoMongoDB Atlas performance advisor has this baked in: https://www.mongodb.com/docs/atlas/performance-advisor/#index-suggestions https://www.mongodb.com/docs/atlas/performance-advisor/#inde... > The Performance Advisor monitors queries that MongoDB considers slow and suggests new indexes to improve query performance
- winrid 3y agoOps Manager (paid tool) kinda supports this but in my experience it misses a lot of obvious suggestions.
- pkiv 3y agoThe supabase team always delivers. Excited to give this a try!
- sbstp 3y agoI've often thought that a database that could automatically detect slow queries and create the necessary indexes would be neat. You run a load test on your application, which in turns calls the database and you collect all the queries it makes. Then the database automatically adjusts itself.
- ComodoHacker 3y agoBig Guys do this. For big bucks, of course.
- tuwtuwtuwtuw 3y ago> big bucks You get that feature in Azure SQL Database for $5/month.
- tuwtuwtuwtuw 3y agoThat exists in Microsoft SQL Server. It can create new indexes, drop unused indexes, change query plans when it detect degradation and so on.
- BrentOzar 3y agoSource? I’ve been working with SQL Server for a couple of decades and I don’t believe it will automatically create or drop indexes under any circumstances. You might be thinking of Azure SQL DB.
- Ciantic 3y ago"Automatic tuning, introduced in SQL Server 2017 (14.x), notifies you whenever a potential performance issue is detected and lets you apply corrective actions, or lets the Database Engine automatically fix performance problems." [1] I have used this in Azure SQL too, but according to that it should be in SQL Server. https://learn.microsoft.com/en-us/sql/relational-databases/automatic-tuning/automatic-tuning?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/a...
- ComodoHacker 3y agoThis doesn't seem to consider or give insight on any tradeoffs involved. Also, the underlying extension, HypoPG, doesn't seem to collect any statistics on data to influence query planner.
- CAP_NET_ADMIN 3y agoIf you have some PostgreSQL performance issues, I'd recommend checking out PGAnalyze - they've offered a much more advanced index advisor for some time now. My company is a paid customer since around 2020 and we are very satisfied, easily beats the Datadog's (which we use for the rest of our infra and apps) observability offering for PostgreSQL.
- deleted 3y ago[deleted]
- allan_s 3y agoRe: performance issue I used to think that performance issue in relational database was always a matter of : * missing indexes * non-used indexes due to query order (where A, B instead of B, A) But we had the case recently where we optimized a query in postgresql which was taking 100% of cpu during 1s (enough to trigger our alerting) by simply splitting a OR in two separate query. So if you are looking for optimisation it may be good to know about "OR is bad". The two queries run in some ms both.
- xvinci 3y agoI'm sorry but you just cannot say that "OR is bad" - it being a key part of SQL . It's most likely your use that is bad (e.g. your intermediate result exceeding some cache size). But "bad performance always due to indexes" gives a hint that you are somewhat new: No, bad performance in my experience was almost always due to developers either not understanding their ORM framework, or writing too expensive queries with or without index. Just adding indexes seldom solved the problem (maybe 1/5 of the time).
- mrklol 3y agoOR is indeed not bad, but you have to think about when to use it. It can easily make queries slower compared to other operators. That’s exactly why we have the terms "ugly OR" / "bad OR".
- 3y ago
- Bishonen88 3y agoHow does one install this on a aws rds instance?
- kiwicopple 3y agohypopg is now supported in RDS so the steps would be: 1. Enable hypopg create extension if not exists hypopg; 2. Copy/paste the plpgsql file: https://github.com/supabase/index_advisor/blob/main/index_advisor--0.2.0.sql https://github.com/supabase/index_advisor/blob/main/index_ad... We are also developing the Trusted Language Extension with the RDS team, so at some point it should be easier to do this through database.dev: https://database.dev/olirice/index_advisor https://database.dev/olirice/index_advisor
- crorella 3y agoInteresting! We did something similar for distributed query engines like presto and Spark, instead of indexes we use partitions and buckets, the benefits are less compute, time and money.
- jensenbox 3y agodexter uses a similar premise with hypog. https://github.com/ankane/dexter https://github.com/ankane/dexter
- dveeden2 3y agoThis is somewhat similar to TiAdvisor for TiDB, which also uses a hypothetical method. https://www.pingcap.com/blog/introducing-tiadvisor-automated-tidb-index-discovery/ https://www.pingcap.com/blog/introducing-tiadvisor-automated...
- fforflo 3y agoThe convenient thing about this is that it's written in vanilla Pl/PgSQL. It can be tempting to copy the `index_advisor(text)`function in a session and start hard-coding stuff and heuristics :D . Most meaningful extensions need to be compiled, installed, created dropped.
- victorbjorklund 3y agoOh that is indeed very nice
- pokipoke 3y agoYou still need to install hypopg which is C extension
- l5870uoo9y 3y agoThis can be optimized both easier and further[0]. How is case-sensitivity optimally handled? Should the query only match exact titles? [0]: https://www.sqlai.ai/snippets/cluzdmi8w006d53gt82mguaga https://www.sqlai.ai/snippets/cluzdmi8w006d53gt82mguaga
- gregw2 3y agoIs this aware of parent and child inherited tables?
- Ozzie_osman 3y agoMy team has been using Pganalyze (which has its own index advisor), and honestly, it's one of my favorite technical tools ever. I don't know how you could run a large-scale postgres setup without it. In addition to index advice, it also shows top queries, vacuums, etc, and monitors for a whole host of other issues out of the box. They also have a ton of great content on their blog (5mins of postgres) where the founder will find blog posts by different authors/companies and analyze them in depth.
- lfittl 3y agoThanks for the kind words! For anyone interested in how pganalyze's approach compares to this extension (and other alternatives like dexter, or using HypoPG directly), I gave a talk with my colleague Philippe last year at PgCon that describes how we use constraint programming and CP-SAT for dealing with the trade-off between index write overhead and read performance improvement, across multiple queries on a table: https://www.pgcon.org/events/pgcon_2023/schedule/session/422-automating-index-selection-using-constraint-programming/ https://www.pgcon.org/events/pgcon_2023/schedule/session/422...
- polivier 3y agoIf anyone has some background in optimization and is interested in the more technical side of things, I gave a talk at JOPT 2023, comparing MIP and CP approaches for this problem. The slides can be found here: https://github.com/PhilippeOlivier/jopt2023 https://github.com/PhilippeOlivier/jopt2023
- Rapzid 3y agoThis is awesome! Was the talk recording by chance?
- lfittl 3y agoYep, there is a "link to video" link on the talk page - here is the direct link: https://www.youtube.com/watch?v=pGN_pORKtSQ https://www.youtube.com/watch?v=pGN_pORKtSQ We also did a more recent webinar that has some slight revisions on top of that talk, recording available in our docs: https://pganalyze.com/docs/indexing-engine/cp-model https://pganalyze.com/docs/indexing-engine/cp-model
- brightball 3y agoWould be great if there was something you could run on tables to recommend more space efficient data types based on what’s actually stored in the table.
- chrisandchris 3y agoYou store numbers, all positive and up to 5k (Let's say it's all your income). So you'll get recommended ushort. Now you'll win the lottery (pun intended). A tool could only recommend that the stored data would fit another datatype. But only business can tell you whether thats true. I only see disadvantages or micro-optimizations.
- klysm 3y agoI can’t imagine a concrete example of this
- jpgvm 3y agoExamples are easy, usefulness... harder to imagine. Simple example, bigint column where all values would fit in smallint or if only 0/1 are present then boolean. For a more complex idea if a large number of boolean columns are present in a table suggest packing them into integer/bigint as appropriate or bit(n) if individual querying/indexing via bit operators is needed. There are many ways to claw back bytes on disk from PostgreSQL if you really need to and a lot of them could be suggested automatically. The reason I say usefulness is harder to imagine is I don't know of anyone that would want to do this but wouldn't know how or where to look for these strategies. It's as if awareness of the need is commensurate with ability to resolve it.
- klysm 3y agoOkay column type I can see, but data structure?
- jpgvm 3y agoDepends what you mean by data structure. If you mean across multiple tables the most obvious answer is a tool that helps improve normalisation. Otherwise I'm not really sure what else you can mean by data structure that isn't how the data is stored on disk or how it's arranged into relations.
- vvern 3y agoCockroachDB has a similar feature built in. It takes existing queries which are slow and then analyzes hypothetical indexes for better query plans and suggests them. You can add them in the console ui with one click. It’s pretty useful.
- danolivo 3y agoThe term ‘slow’ is too relational and not strong. I guess, we should look up for queries, which can be potentially faster - see into estimation errors or number of data pages involved into the query.
- icang888 3y agoTwslive
- danolivo 3y agoHaving creation advice, the extension obviously must provide candidates to delete and, less obvious, candidates to merge some indexes.
- cpursley 3y agoGood stuff, just added this and some others mentioned in this thread to my Postgres Is Enough listicle: https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f06dbb#performance-tuning https://gist.github.com/cpursley/c8fb81fe8a7e5df038158bdfe0f...
- deleted 3y ago[deleted]
- asah 3y agoNice. This is 404ing: https://levelup.gitconnected.com/how-cloudflare-achieved-55-million-requests-per-second-with-just-15-postgresql-clusters-2d8a28ffd100 https://levelup.gitconnected.com/how-cloudflare-achieved-55-...