5 ms·

I sometimes wish I could directly write a plan for my query instead of relying on the db to come up with something that matches it (OLAP). The other 90% percent

by lixtra 8y ago

I sometimes wish I could directly write a plan for my query instead of relying on the db to come up with something that matches it (OLAP). The other 90% percent I’m happy that it just works.

TomMarius 8y ago
IMO your use case would suit only very static needs that maybe you should consider using a completely different thing than a SQL database. I see the abstraction you're talking about as unnecessary as the whole point - and while it's true that an optional feature that would let you do that could be nice, I fail to see any point of that.
- lixtra 8y ago
  If my desire is so wrong, why do things like index hints exist? And the very static case is the easy one in OLAP. You build a star schema that is so simple that the planner could not get it wrong. To fill this schema you use some ETL tool that very much resembles a static plan. In the dynamic, exploratory phase (does it make sense to look at the data this way) you have to deal with the nasty complicated queries.
  - hobs 8y ago
    "So simple the planner could not get it wrong" - and then you add a billion rows, or the business needs schema changes, or, or, or... If you want to have that exact behavior I'd agree with the gp, we are talking about data access, but not all data access has to be a SQL database. Index hints are just that, hints, they are not commands - because there are tipping points that they just dont make sense anymore.
crazygringo 8y ago
I agree 1,000% -- I've thought about writing an article on it. There's a huge difference between one-time queries (let the planner figure it out) versus production queries. I've been bitten in the past by the SQL planner failing as it reaches some threshold and suddenly stops using an index it was using before, etc., and queries go from taking milliseconds to seconds. It's rare, but it happens, and the results can be catastrophic -- usually because it starts ignoring an index because it (wrongly) thinks the index would slow it down -- planners aren't perfect. I'd much rather specify in advance the query plan to handle what I knew would be the eventual biggest-case scale of data: to always use X index (forced, not just hinted), and to deal, in advance, with whether or not sorting will always be possible in memory or if it will need to be done in a swap file on disk, and at what points filtering happens. I actually don't care if this slows down my (already blazing fast) queries 10x or even 100x when the database is small (e.g. by sorting on disk instead of in memory, even when just 10 rows -- or using the index even when clearly unnecessary with just 10 rows). It buys me the peace of mind that there will be no discontinuities in performance in production, no surprises. Of course this isn't for basic grab-a-row queries: I'm talking about more complex queries with all the bells and whistles (subqueries, multiple joins, filters, etc.) where the database's planner goes haywire.
- evanweaver 8y ago
  This is FaunaDB’s strategy. Indexes and views are explicitly referenced and thus every query is implicitly also its own query plan, with local optimizations only. No discontinuous planning events.
mrighele 8y ago
What database are you using? Oracle [1] and MySQL [2] for example let you embed hints in the query in order to change the execution plan. I think that PostreSQL doesn't have such a feature though (at least it did not a few years ago). [1] https://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#i8327 https://docs.oracle.com/cd/B19306_01/server.102/b14211/hints... [2] https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
- iracic 8y ago
  You can also record and save plan you are happy with. So, no need to fix hints in code. It is more flexible and may be practical when you upgrade version or environment changes.
  - lixtra 8y ago
    Can you do that in MySQL? For Oracle you AFIAK need special permission to save a plan. In larger corporations this means you have call the db admin which makes total sense for OLTP (and is a good feature of SQL dbs). If you’re exploring something in OLAP this is not so great.
deleted 8y ago
[deleted]

by lixtra 8y ago

I sometimes wish I could directly write a plan for my query instead of relying on the db to come up with something that matches it (OLAP). The other 90% percent I’m happy that it just works.

TomMarius 8y ago

IMO your use case would suit only very static needs that maybe you should consider using a completely different thing than a SQL database. I see the abstraction you're talking about as unnecessary as the whole point - and while it's true that an optional feature that would let you do that could be nice, I fail to see any point of that.

lixtra 8y ago
If my desire is so wrong, why do things like index hints exist? And the very static case is the easy one in OLAP. You build a star schema that is so simple that the planner could not get it wrong. To fill this schema you use some ETL tool that very much resembles a static plan. In the dynamic, exploratory phase (does it make sense to look at the data this way) you have to deal with the nasty complicated queries.
- hobs 8y ago
  "So simple the planner could not get it wrong" - and then you add a billion rows, or the business needs schema changes, or, or, or... If you want to have that exact behavior I'd agree with the gp, we are talking about data access, but not all data access has to be a SQL database. Index hints are just that, hints, they are not commands - because there are tipping points that they just dont make sense anymore.

crazygringo 8y ago

I agree 1,000% -- I've thought about writing an article on it. There's a huge difference between one-time queries (let the planner figure it out) versus production queries. I've been bitten in the past by the SQL planner failing as it reaches some threshold and suddenly stops using an index it was using before, etc., and queries go from taking milliseconds to seconds. It's rare, but it happens, and the results can be catastrophic -- usually because it starts ignoring an index because it (wrongly) thinks the index would slow it down -- planners aren't perfect. I'd much rather specify in advance the query plan to handle what I knew would be the eventual biggest-case scale of data: to always use X index (forced, not just hinted), and to deal, in advance, with whether or not sorting will always be possible in memory or if it will need to be done in a swap file on disk, and at what points filtering happens. I actually don't care if this slows down my (already blazing fast) queries 10x or even 100x when the database is small (e.g. by sorting on disk instead of in memory, even when just 10 rows -- or using the index even when clearly unnecessary with just 10 rows). It buys me the peace of mind that there will be no discontinuities in performance in production, no surprises. Of course this isn't for basic grab-a-row queries: I'm talking about more complex queries with all the bells and whistles (subqueries, multiple joins, filters, etc.) where the database's planner goes haywire.

evanweaver 8y ago
This is FaunaDB’s strategy. Indexes and views are explicitly referenced and thus every query is implicitly also its own query plan, with local optimizations only. No discontinuous planning events.

mrighele 8y ago

What database are you using? Oracle [1] and MySQL [2] for example let you embed hints in the query in order to change the execution plan. I think that PostreSQL doesn't have such a feature though (at least it did not a few years ago). [1] https://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm#i8327 https://docs.oracle.com/cd/B19306_01/server.102/b14211/hints... [2] https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html

iracic 8y ago
You can also record and save plan you are happy with. So, no need to fix hints in code. It is more flexible and may be practical when you upgrade version or environment changes.
- lixtra 8y ago
  Can you do that in MySQL? For Oracle you AFIAK need special permission to save a plan. In larger corporations this means you have call the db admin which makes total sense for OLTP (and is a good feature of SQL dbs). If you’re exploring something in OLAP this is not so great.

deleted 8y ago

[deleted]