Introducing HypoPG, hypothetical indexes for PostgreSQL
postgresql.org
Introducing HypoPG, hypothetical indexes for PostgreSQL
1–10 of 38 posts
Re: Introducing HypoPG, hypothetical indexes for PostgreSQL
#2I'd always wondered why query engines don't create such temporary indices, for example in the case of large subquery queries, where creating and index AND doing the query with an index runs way faster in total than waiting for it to run without the index. Any insights on this? I guess predicting the gain could be difficult.
Re: Introducing HypoPG, hypothetical indexes for PostgreSQL
#3Cool one! Thankfully postgres allows creating indexes concurrently (without blocking writes) but 'hypothetical indexes' seem more convenient.
What would be even cooler though is the ability to see multiple plans considered (and rejected) by the planner with all associated costs. It often takes a lot of painful guesswork to understand why particular superior plan is not used.
Re: Introducing HypoPG, hypothetical indexes for PostgreSQL
#4Very cool. I'll write a Django package that wraps around this feature.
Re: Introducing HypoPG, hypothetical indexes for PostgreSQL
#5I'd always wondered why query engines don't create such temporary indices, for example in the case of large subquery queries, where creating and index AND doing the query with an index runs way faster in total than waiting for it to run without the index. Any insights on this? I guess predicting the gain could be difficult.
There shouldn't be a situation where reading the data + creating the index would be less intensive than just reading the data.
This is from the SQL server perspective so I apologize if all this doesn't translate 100%. Let's say the column you're filtering on is currently not in any index. That field then needs to be pulled out of the clustered index's leaf pages. Since it's unsorted, we'd need to read the entire table- a full scan.
To build the temporary index we'd also need to read in all of the data, so the same amount of work from that perspective, but then we'd have to sort it all, which is very intensive.
If there's a situation where we're doing something analogous to a inner loop join in SQL Server, but it's doing full scans for each iteration, that's a problem with the query optimizer.
Re: Introducing HypoPG, hypothetical indexes for PostgreSQL
#6I'd always wondered why query engines don't create such temporary indices, for example in the case of large subquery queries, where creating and index AND doing the query with an index runs way faster in total than waiting for it to run without the index. Any insights on this? I guess predicting the gain could be difficult.
There shouldn't be a situation where reading the data + creating the index would be less intensive than just reading the data. This is from the SQL server perspective so I apologize if all this doesn't translate 100%. Let's say the column you're filtering on is currently not in any index. That field then needs to be pulled out of the clustered index's leaf pages. Since it's unsorted, we'd need to read the entire tabl…
Very true. That said, there are almost always situations where reading the data + creating the index + using it N times would be less intensive than just reading the data N times, for very small N.
I'd love to see Postgres or MongoDB with an index-suggesting logger - I'd pay the cost of the logging to have it run on every query, then a background thread would figure out what the "hot spot" types of queries are, figure out what indices to create concurrently, and show it to me in a web interface where I can click a button and create the indices I need concurrently during a low-traffic period of time.
Does anything out there like this exist?
Re: Introducing HypoPG, hypothetical indexes for PostgreSQL
#7This is quite excellent for those of us doing research in physical schema design. As far as I know, this is the first "what-if" analysis tool integrated into an open source database. Although if there are others, I'd love to know about them :)
Re: Introducing HypoPG, hypothetical indexes for PostgreSQL
#8I'd always wondered why query engines don't create such temporary indices, for example in the case of large subquery queries, where creating and index AND doing the query with an index runs way faster in total than waiting for it to run without the index. Any insights on this? I guess predicting the gain could be difficult.
Watch out, the extension does not create "temporary indices". They can't be used in queries.
It only creates virtual indices, whose only purpose is to observe how the query optimizer would behave, if they were real.
I find it quite a curious idea, although an important parameter of the indices is the cardinality, which I don't see customizable.
Re: Introducing HypoPG, hypothetical indexes for PostgreSQL
#9Earlier quoted context omitted.
There shouldn't be a situation where reading the data + creating the index would be less intensive than just reading the data. This is from the SQL server perspective so I apologize if all this doesn't translate 100%. Let's say the column you're filtering on is currently not in any index. That field then needs to be pulled out of the clustered index's leaf pages. Since it's unsorted, we'd need to read the entire tabl…
Very true. That said, there are almost always situations where reading the data + creating the index + using it N times would be less intensive than just reading the data N times, for very small N. I'd love to see Postgres or MongoDB with an index-suggesting logger - I'd pay the cost of the logging to have it run on every query, then a background thread would figure out what the "hot spot" types of queries are, figur…
There's a python package called dex (https://github.com/mongolab/dex) that helps assist with index recommendations by looking at mongo's oplog and query response times. It's not perfect but better than nothing for alerting people to a missing index. Unfortunately I think it is not under active development anymore and beginning to fall behind with changes in mongodb.
Re: Introducing HypoPG, hypothetical indexes for PostgreSQL
#10Earlier quoted context omitted.
There shouldn't be a situation where reading the data + creating the index would be less intensive than just reading the data. This is from the SQL server perspective so I apologize if all this doesn't translate 100%. Let's say the column you're filtering on is currently not in any index. That field then needs to be pulled out of the clustered index's leaf pages. Since it's unsorted, we'd need to read the entire tabl…
Very true. That said, there are almost always situations where reading the data + creating the index + using it N times would be less intensive than just reading the data N times, for very small N. I'd love to see Postgres or MongoDB with an index-suggesting logger - I'd pay the cost of the logging to have it run on every query, then a background thread would figure out what the "hot spot" types of queries are, figur…
The PoWA project (https://dalibo.github.io/powa/) + pg_qualstats extension is doing most of this work.
The pg_qualstats extension will gather statistics on WHERE clauses (number of execution, selectivity...) on the fly, and the powa UI can show you which are the most expensive queries, and suggest index creation based on your real workload. It's then your choice to create the indexes.
hypopg should be added soon to compare query EXPLAIN with and without the hypothetical indexes.