Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

The SQLite Index Suggester

sqlite.org

Post reply on HN
Print view View original

21–26 of 26 posts

Re: The SQLite Index Suggester

#21

Post by rileymat2 »

Someone wrote: post #12
yvan wrote: post #9

I find it interesting, as recently I was reading about how complex it would/could be to create an index suggester. https://www.depesz.com/2021/10/22/why-is-it-hard-to-automati...

It’s like writing a compiler or interpreter: writing one is easy; writing a good one extremely hard. This suggester isn’t very good. It takes a single query and suggests indexes for it. A good one would take a mix of queries and suggest a set of indexes, also considering the impact on write speed of additional indexes (table updates often need to update indexes, too) For the example in this article, if the table is l…

>It takes a single query and suggests indexes for it. A good one would take a mix of queries and suggest a set of indexes, also considering the impact on write speed of additional indexes (table updates often need to update indexes, too)

This is my pet peeve with SQL Server SSMS will give you a missing index suggestion and cost... the problem is inexperienced people will take the suggestion as is and create way too many highly specialized indexes over time.

Re: The SQLite Index Suggester

#23

Post by slaymaker1907 »

Someone wrote: post #12
yvan wrote: post #9

I find it interesting, as recently I was reading about how complex it would/could be to create an index suggester. https://www.depesz.com/2021/10/22/why-is-it-hard-to-automati...

It’s like writing a compiler or interpreter: writing one is easy; writing a good one extremely hard. This suggester isn’t very good. It takes a single query and suggests indexes for it. A good one would take a mix of queries and suggest a set of indexes, also considering the impact on write speed of additional indexes (table updates often need to update indexes, too) For the example in this article, if the table is l…

Yeah, a good autoindexer might even suggest you get rid of certain indices if they don't seem very important due to the overhead you see on inserts.

Re: The SQLite Index Suggester

#24

Post by ayende »

krylon wrote: post #2

This sounds really cool! I've sometimes wondered why server-based RDBMSs don't offer something like this. Is it too hard to implement? Or did people just not think of it? Or do they have something like this and I just never learned about it?

RavenDB has this feature, but it will also go ahead and create the relevant indexes needed on the fly.

It is able to do so without harming production performance.

See: https://ravendb.net/learn/inside-ravendb-book/reader/4.0/9-q...

Re: The SQLite Index Suggester

#25

Post by SomeOther »

beachy wrote: post #14
krylon wrote: post #2

This sounds really cool! I've sometimes wondered why server-based RDBMSs don't offer something like this. Is it too hard to implement? Or did people just not think of it? Or do they have something like this and I just never learned about it?

Oracle (and likely others) have had this since well into the last century. As you might expect since it can save a huge amount of money on a large database, it's part of an (expensive) add on.

Oracle can also do it autonomously if you have the super duper expensive package. It finds useful indexes based on what's queried regularly, tries them out behind the scenes to see if it'd improve things, and then creates them if they'd be useful.

Re: The SQLite Index Suggester

#26

Post by stavros »

yvan wrote: post #9

I find it interesting, as recently I was reading about how complex it would/could be to create an index suggester. https://www.depesz.com/2021/10/22/why-is-it-hard-to-automati...

That's a great primer on performant indexes, thanks!
Post reply on HN