5 ms·
I'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 appl
by sbstp 2y ago
I'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 2y agoBig Guys do this. For big bucks, of course.
- tuwtuwtuwtuw 2y ago> big bucks You get that feature in Azure SQL Database for $5/month.
- tuwtuwtuwtuw 2y 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 2y 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 2y 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...
- couchand 2y agoGood link! > Automatic index management identifies indexes that should be added in your database, and indexes that should be removed. Applies to: Azure SQL Database
- BrentOzar 2y agoRead that link carefully: only automatic plan regression is available in SQL Server, not the automatic index tuning portion. The index tuning portion only applies to Azure SQL DB.
- tuwtuwtuwtuw 2y agoWhat's the point of asking for a source when you would find it on Google in one minute? Odd way of learning. Not like I brought up some debated viewpoint.
- hobs 2y agoProbably because most of the stuff you'd find in the top search results would include the GP's name. Just a few sentences later "Automatic tuning in Azure SQL Database also creates necessary indexes and drops unused indexes" - that's not in on-prem SQL Server.
- simplyinfinity 2y agoGoogle the name of the person you're replying to :)
- radicalbyte 2y agoThey've had a non-automatic "query advisor" in there forever, it operated on profiling data and was highly effective.
- taspeotis 2y agoThat’s an Azure SQL thing, not MSSQL.
- elric 2y agoI'm sure the database could, but it doesn't mean the database should. Indexes come at the cost of extra disk space, slower inserts, and slower updates. In some cases, some slower queries might be an acceptable tradeoff. In other cases, maybe not. It depends.
- kiwicopple 2y agothis is our posture for this extension on the supabase platform. we could automate the creation of the indexes using the Index Advisor, but we feel it would be better to expose the possible indexes to the user and let them choose
- gneray 2y agothis is the way ^^
- dmurray 2y agoYou could tell it "you have a budget of X GB for disk space, choose the indexes that best optimize the queries given the budget cap." Not perfect, because some queries may be more time-critical than others. You could even annotate every query (INSERT and UPDATE as well as SELECT) with the dollar amount per execution you're willing to pay to make it 100ms faster, or accept to make it 100ms slower. Then let it know the marginal dollar cost of adding index storage, throw this all into a constraint solver and add the indexes which are compatible with your pricing.
- d0100 2y agoAre the trade-offs measurable? If they are the database could just undo the index... Not just indexing, but table partitions, materialized views, keeping things in-memory...
- remus 2y ago> Are the trade-offs measurable? Yes, but you need the context about what is the correct tradeoff for your use case. If you've got a service that depends on fast writes then adding latency via extra indices for improved read speed may not be an acceptable trade off. It depends on your application though.
- masklinn 2y agoBecause indexes have costs you need a much more complicated system which can feed back into itself and downgrade probationary indexes back to unindexed.
- fulafel 2y agoSeveral databases index everything, needed or not. (And sometimes have mechanisms to force it off for some specific data)
- freedomben 2y agoEven this isn't sufficient, because some problems with over-indexing don't become apparent until the size of a table gets much larger, which only happens a drop at a time. I suppose if it was always probationary and continually being evaluated, at some point it could recognize that for example INSERTs are now taking 1000x longer than they were 2 years ago. But that feels like a never-ending battle against corner cases, and any automatic actions it takes add significant complexity to the person debugging later.
- arronax 2y agoOracle DB is, or was, very close to that with its query profiles, baselines, and query patches. It wasn't automatic back in 2014 when I last worked on it, but all the tools were there. Heck, it was possible to completely rewrite a bad query on the fly and execute a re-written variant. I suppose it all stems from the fact that Oracle is regularly used under massive black boxes, including the EBS. Also, the problem with automatic indexing is that it only gets you so far, and any index can, in theory, mess up another query that is perfectly fine. Optimizers aren't omniscient. In addition, there are other knobs in the database, which affect performance. I suppose, a wider approach than just looking at indexes would be more successful. Like Ottertune, for example.
- Tostino 2y agoThe problem of new indexes messing up otherwise good queries is something I've battled on and off for the past decade with Postgres. Definitely annoying.
- rand_r 2y agoHow would an index mess up another query? AFAIK indexes would only hurt write performance marginally per index, but most slow queries are read-only. I’ve tended to just add indexes as I go without thinking about it and haven’t run into issues, so genuinely curious.
- adamcharnock 2y agoWhile I don’t recall running into issues either, I can certainly see that a new index could cause the query planner to make a different decision. And that decision could - in some cases - end up being worse that the previous behaviour. I definitely have seen the query planing make some peculiar choices in the past.
- Tostino 2y agoThis is what I ran into. Often times they were indexes with a similar cost as another, and that caused issues. I think the main index type that bit me are the ones created by exclusion constraints. Often times it looks to the planner like "the right" index to use, but there is another (btree) that is way cheaper...the exclusion constraint is just there to ensure consistency. In those cases to fix things, I added a WHERE clause to the index (e.g. WHERE 1=1), and the planner wouldn't consider that index unless it saw that same 1=1 condition in the queries WHERE clause.
- b3lm0nt 2y agoAndrew Kane built dexter, which is an automatic indexer for Postgres. https://github.com/ankane/dexter https://github.com/ankane/dexter https://ankane.org/introducing-dexter https://ankane.org/introducing-dexter
- ed_balls 2y agoDefault DB for App Engine (NDB) has this feature. Implicit indexes are tad annoying.
- GordonS 2y agoI might be misremembering, but IIRC RavenDB does this (it's a commercial document DB, written in C#).