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

Introducing HypoPG, hypothetical indexes for PostgreSQL

postgresql.org

Post reply on HN
Print view View original

11–20 of 38 posts

Re: Introducing HypoPG, hypothetical indexes for PostgreSQL

#11

Post by MSM »

btown wrote: post #6
MSM wrote: post #5

Earlier 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…

SQL Server has all this information saved (I'm not paid by Microsoft, I swear). I would suspect something is available for other engines as well.

For example, I can easily grab (what it thinks are) the top 10 most impactful missing indexes and create them. The big issue is that, well, they can be really dumb when looking at a macro level. Do I often query a 10 column table for 8 of the columns? It will likely recommend I index those 8 columns. In a vacuum that might be a great index for this specific query, but now I'm basically maintaining two copies of the full table.

To be able to weigh these pros and cons would be a really nice feat, then DBAs will have to abandon the "It Depends" mantra!

Re: Introducing HypoPG, hypothetical indexes for PostgreSQL

#12

Post by daamien »

MSM wrote: post #5
makmanalp wrote: post #2

I'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…

Temporary index and Hypothetical index are two different things.

Temporary index is a real index that's usually dynamically created by the optimizer to speed up a query. But it's kind of useless because most of the time the cost of creating a one-time index exceed the cost of not using it. Afaik only DB2 as this feature

http://www.centerfieldtechnology.com/PDFs/DB2%20Temp%20Index...

An hypotetical index is just a way to "trick" the planner into thinking that there's an index and see what the query plan would be if that index really existed. The cost of creating this is close to zero.

Re: Introducing HypoPG, hypothetical indexes for PostgreSQL

#13

Post by pizza234 »

MSM wrote: post #5
makmanalp wrote: post #2

I'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…

I think the parent refers to a specific optimization that MySQL doesn't perform:

SELECT ... FROM ... WHERE field IN ( SELECT field2 FROM ... )

MySQL doesn't acceptably optimize such queries (at v5.6, as far as I remember).

This is a more complex case than the one you're pointing - the field2 records may be an intermediate result, logically and/or physically.

For example of the latter, in a typical mysql execution with a relatively large number of records, the subquery records are likely to reside on a temporary swapped table. In this case, the optimizer would need to be able to create the intermediate table with optimal data structure[s], which (if I remember correctly) version 5.6 is not capable of.

Re: Introducing HypoPG, hypothetical indexes for PostgreSQL

#14

Post by rosser »

MSM wrote: post #5
makmanalp wrote: post #2

I'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…

Your scenario is valid for "index organized tables", where the data for a given row is stored in one of the index's leaf pages. Synthesizing an index on-demand for those involves an extraordinary amount of random IO.

In postgres, a table's data is stored in the "heap" (a disk file representing the table, itself; indexes are separate disk files). Consequently, you can do a sequential pass through the table, and then calculate/build the index in RAM (assuming you have sufficient "maintenance_work_mem", in postgres configuration terms, otherwise you'll spill to disk at this step, too), and then write it out sequentially as well — and only then walk the index depth-first to get to the leaf page that points to the disk page in the heap that contains the row you're selecting.

(And even that random depth-first traversal of the index will probably actually happen in RAM, because postgres significantly defers to the kernel's buffer cache to mitigate IO costs, and the index you're traversing is pretty much guaranteed to be cached at this point.)

EDIT: It would apply to MySQL using InnoDB, however, as IIRC that does store table data on the leave pages of the primary key index. Can someone corroborate that?

Re: Introducing HypoPG, hypothetical indexes for PostgreSQL

#15

Post by kbenson »

pizza234 wrote: post #8
makmanalp wrote: post #2

I'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.

Yes, in this case hypothetical means conjecture. "Let's get information about how the system would function if there existed an index as such..."

Re: Introducing HypoPG, hypothetical indexes for PostgreSQL

#17

Post by hawkice »

MSM wrote: post #5
makmanalp wrote: post #2

I'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…

If you do N self-joins in a row, creating the index on the fly would be a good move, because they'd have to read through the same data multiple times. The real world example that springs to mind is comments that can nest up to e.g. 5 layers, where it kinda makes sense to grab it all in one query like that.

Obvious caveats: I would just have a 'top-level-post-id' so I could grab everything I need and use the foreign keys to arrange them in memory, not for discovering the rows I need, also wowzers doing _any_ joins without an index is silly, forget multiple in one query.

Re: Introducing HypoPG, hypothetical indexes for PostgreSQL

#18

Post by woodman »

makmanalp wrote: post #2

I'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.

SQLite does this, autoindex[0].

"When no indices are available to aid the evaluation of a query, SQLite might create an automatic index that lasts only for the duration of a single SQL statement. Since the cost of constructing the automatic index is O(NlogN) (where N is the number of entries in the table) and the cost of doing a full table scan is only O(N), an automatic index will only be created if SQLite expects that the lookup will be run more than logN times during the course of the SQL statement."

[0] http://www.sqlite.org/optoverview.html#autoindex

Re: Introducing HypoPG, hypothetical indexes for PostgreSQL

#19

Post by pizza234 »

MSM wrote: post #11
btown wrote: post #6

Earlier quoted context omitted.

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…

SQL Server has all this information saved (I'm not paid by Microsoft, I swear). I would suspect something is available for other engines as well. For example, I can easily grab (what it thinks are) the top 10 most impactful missing indexes and create them. The big issue is that, well, they can be really dumb when looking at a macro level. Do I often query a 10 column table for 8 of the columns? It will likely recomme…

I think indexing is ultimately a trade-off design; the end effect of it is a decision which belongs to a human - what do we make faster at the expense of what?

Probably the best thing a "machine" can do is to give tools to easily perform a reasonably accurate and extensive analysis, but I think any major RDBMS has "good enough" tools to do so.

Interestingly, I think it's important to think who is the target of such tools - the casual developer? the novice dba? the expert dba who works on large datasets? I think even theoretically advanced tools would help only the first case; the other two need to know very well what they're working with.

Re: Introducing HypoPG, hypothetical indexes for PostgreSQL

#20

Post by willu »

One of my major hang-ups about transitioning away from SQL Server to Postgres is the productivity that comes from tools like Profiler and Database Engine Tuning Advisor (which uses hypothetical indexes to find performance improvements). Can anyone suggest analogous tools for Postgres?
Post reply on HN