[HN Gopher] Why Can't Database Tables Index Themselves? (2006)
___________________________________________________________________
Why Can't Database Tables Index Themselves? (2006)
Author : SPBS
Score : 126 points
Date : 2022-07-05 17:08 UTC (5 hours ago)
(HTM) web link (blog.codinghorror.com)
(TXT) w3m dump (blog.codinghorror.com)
| ab_testing wrote:
| I thought Oracle already has this feature -
| https://www.oracle.com/news/connect/oracle-database-automati...
| KronisLV wrote:
| It does, though are some restrictions in regards on which
| versions it can run on, unless you want to enable features that
| are not officially supported: https://oracle-
| base.com/articles/19c/automatic-indexing-19c
|
| My issue is that you cannot (easily) delete the automatically
| generated indices, should you want to do that for whatever
| reason. Of course, Oracle will try to only enable the indices
| that will lead to actual performance gains, but when it comes
| to removing the automatically created ones altogether, the best
| that you can do is: DBMS_AUTO_INDEX.CONFIGURE
| ('AUTO_INDEX_RETENTION_FOR_AUTO', '1');
|
| Consider that you have a few different environments, all of the
| same database schema with a few hundred tables in it. You might
| want to take one of those environments (a development
| environment) and enable the feature there, have a bunch of
| indices be automatically created, take the DDL for those and
| turn them into migrations that can be run against other
| environments (any number of testing environments, eventually
| production) either manually or through some automated SQL
| migration solution.
|
| The problem is that you can't easily get rid of all the
| automatically generated ones so that you could test this
| migration against the same environment (basically create those
| same indices through your own SQL).
| elif wrote:
| It's possible for sure.
|
| The problem is that in such a scenario, there is no requirement
| or even recommendation for query writers to structure queries
| with regard for existing indices. You will rapidly end up with
| too many partially-overlapping, including the overhead to
| maintain them.
| marcosdumay wrote:
| On almost every time, this is still a large gain. And on both
| cases, if you have complete coordination of the developers, you
| get the same result.
|
| You will probably want a "what query uses this index?" feature,
| that is the other side of the coin of "what indexes make my
| queries faster?"
| jspaetzel wrote:
| The best comment is on the page itself, "DBAs don't like the DB
| server doing things that they didn't expect."
|
| Imagine how much less predictable your database becomes when it's
| trying to be "smart". I wouldn't want to be oncall for that
| thing.
| RajT88 wrote:
| Makes me wonder if you could use a SQL Injection exploit to
| maliciously trick out the index generation feature, and create
| a totally shit index and cause a DOS attack.
| tester756 wrote:
| If you have already found SQL Injection
|
| then why not do it yourself?
|
| or a lot of other bad things?
| jspaetzel wrote:
| Could probably tip weighting to create unnecessary indexes in
| some cases. Really depends on how automatic the index
| creation is, could reek with potential exploits.
| SigmundA wrote:
| The obvious is that an index is a tradeoff, slower writes and
| more disk space for faster reads. Unless the db can know disk
| space and write perf is worth the tradeoff it would be difficult
| for it to make good decision.
|
| If you didn't care about disk space and the db could do async
| indexes but not have stale reads then an automatic index system
| could make sense.
| nikita wrote:
| Disk space is becoming less of an issue in cloud environment
| with the rise of multi-tenant storage: aurora, neon (i'm ceo of
| neon). The cost of index maintenance is real and should be
| considered.
|
| Due to the multivariable nature of the this problem ML is the
| right approach to solve it and the ability to "prove" that it
| will work is also key.
| mejutoco wrote:
| Constraint programming might be an alternative if we want
| "explainability".
| polyrand wrote:
| The SQLite CLI has the .expert mode (marked as experimental) [0].
| Turning it on will suggest indexes for the queries executed.
|
| [0]:
| https://www.sqlite.org/cli.html#index_recommendations_sqlite...
| fiatjaf wrote:
| Here's a database that does much better than this, it creates and
| fine-grainedly updates materialized views of the queries you're
| using: https://github.com/mit-pdos/noria
|
| Here's an excellent interview with the creator:
| https://corecursive.com/030-rethinking-databases-with-jon-gj...
| FuriouslyAdrift wrote:
| Indexes typically only speed up reads while also slowing down
| writes (sometimes massively).
|
| SARGability of the queries is also needed to effectively use the
| indexes.
| fiatjaf wrote:
| If I remember correctly Google AppEngine's NDB had such a thing
| maybe 10 years ago. Every time you queried the database it would
| create an index. I don't know how smart that mechanism was.
|
| I don't know how things are in Google Appengine land now.
| twgraham wrote:
| RavenDB has auto indexes
| gbourne wrote:
| Firestore not only recommends, but requires you to have the index
| or the query will fail.
| sam0x17 wrote:
| This this this a thousand times this I've had buliding a system
| such as this for postgres on my bucket list for almost a decade
| now. Why is this not a thing
| lfittl wrote:
| Doing this well for a production system is a hard problem. The
| sibling comment pointed out HypoPG and Dexter, but I'm not
| familiar with folks using a simple approach like the one Dexter
| implements on a production system. The human element is
| valuable when indexing, to correctly model the workload
| understanding and assess the trade-offs (index write overhead,
| etc).
|
| For context, I've personally been working on an automatic
| indexing system for Postgres for more than a year now [1], and
| whilst I think what we have today is pretty useful, there is
| still work to be done. We've intentionally not yet enabled full
| automation (i.e. actual automatic creation of the indexes on
| the production database), because most people I've talked with
| prefer to review a recommendation and then apply it through
| their regular migration tooling, after making an assessment.
|
| If you ever want to talk more about this, feel free to send me
| an email - I've spent a lot of time thinking about this topic
| :)
|
| [1] https://pganalyze.com/blog/automatic-indexing-system-
| postgre...
| code_biologist wrote:
| There's a lot of good ecosystem stuff around this, though I
| personally can't use most of it as the HypoPG extension isn't
| on AWS RDS. :(
|
| https://github.com/HypoPG/hypopg
|
| _HypoPG is a PostgreSQL extension adding support for
| hypothetical indexes._
|
| _An hypothetical -- or virtual -- index is an index that doesn
| 't really exists, and thus doesn't cost CPU, disk or any
| resource to create. They're useful to know if specific indexes
| can increase performance for problematic queries, since you can
| know if PostgreSQL will use these indexes or not without having
| to spend resources to create them._
|
| With one approach to actually implementing it here:
| https://www.percona.com/blog/2019/07/22/automatic-index-reco...
|
| https://github.com/ankane/dexter is an autoindexer project that
| builds on hypopg, and I believe it can be configured to
| autocreate indices -- it will only suggest them by default.
| nikita wrote:
| One of the reasons is "safety". It's hard to make sure that
| adding an index won't have adverse effects on your app. Adding
| an index invalidates query plans and new plans may or may not
| be as good as the old ones.
|
| I think the enabling technology for this is branching and the
| ability to "fork" a database AND the workload to prove that
| adding an index doesn't break things.
| eurasiantiger wrote:
| So we're back to Kubernetes! ;)
| ElectricalUnion wrote:
| Except that Kubernetes (at least last time I checked it
| did) more or less ignores exactly this hard part - keeping
| state sane and manageable - because it's hard and
| implementation/vendor dependent.
|
| The only reasonably ready "Kubernetes" project I saw
| attempting to solve this was SUSE/Rancher longhorn, and
| that was still somewhat incubating last time I checked.
| gigatexal wrote:
| RavenDB (noSQL) does this already:
| https://ravendb.net/docs/article-page/5.3/csharp/indexes/cre...
| gweinberg wrote:
| I think a good DBA ought to be able to better than an automatic
| process possibly could. The database can't know whether an index
| ought to be unique, you have to understand the data model to know
| that. You might want to make an index multi-column even if you
| aren't using all the columns the first time you make a query, but
| again you need a human-level understanding of the data model to
| do that. And if you already have indexes that work reasonably
| well, you can't expect an automatic optimizer to realize it could
| do even better by replacing existing indices. That being said, an
| automatic indexer could probably be "good enough" in many cases.
| nikita wrote:
| Time an again AI systems outperform humans. This maybe true
| today but not tomorrow
| Daishiman wrote:
| It depends also on what you want to prioritize. Indices add
| insertion overheads. You can have general rules for indices but
| ultimately it comes down to what operations are time-critical.
| Someone wrote:
| Indices also can be extremely large. For example, a
| SELECT foo, bar, baz FROM quux WHERE foo = ?;
|
| will be fastest with an index on _foo_ that also stores _bar_
| and _baz_ (once the index entry is found, the engine won't
| need to do a disk seek to the row data). If that table is
| large and you run SELECT foo FROM quux WHERE
| foo = ?;
|
| (Edited after _zimpenfish_ 's remark about a copy-paste
| error)
|
| more frequently than the former query, just creating an index
| on _foo_ alone may be the better choice. Whether it does
| depend on lots and lots of factors such as query distribution
| (is it worth it to make the monthly report twice as fast at
| the cost of running many other queries 1% slower?), disk
| speed (if you move a database to SSD you should revisit all
| indexing choices), and disk space.
|
| With advanced SQL engines, there's way more choice. Maybe,
| it's best to create an index on the first n character of a
| string field, or an index that only works for equality
| searches, not for phrases such as _foo < 3_, etc, or an index
| on only the data from last month (can be done in some
| databases with partitioned tables)
| zimpenfish wrote:
| > SELECT foo, bar, baz FROM quux WHERE foo = ?;
|
| > SELECT foo, bar, baz FROM quux WHERE foo = ?;
|
| Possibly a typo in the second query since the text
| indicates it should be different from the first?
| msbarnett wrote:
| > I think a good DBA ought to be able to better than an
| automatic process possibly could
|
| Absolutely, although in modern orgs I'd expect this to be the
| domain of backend engineers rather than "a DBA", a mythical
| creature I've never actually seen in real life.
| galdosdi wrote:
| Where I'm from, DBA is just a euphemism for "employee with
| write access to the prod DB"
| kristiandupont wrote:
| Oh they exist alright, I have met them. They are bearded old
| men that rule the kingdom of Oracle in a dusty room that
| smells of stale coffee. If you ask them if you may please
| have a table they respond angrily "NO, you may NOT have a
| table!"
| _eht wrote:
| This made me spit out my stale coffee.
| smrtinsert wrote:
| I sometimes think this was the actual use case being solved
| by the nosql movement.
| ruined wrote:
| all new technology solves a problem, sometimes it's a
| social problem
| gpderetta wrote:
| If a database is expected to be good enough to optimize query
| plans by default, wouldn't you expect it to be also good enough
| to optimize layout/indexing?
| nikita wrote:
| Traditionally query optimizers are focused on producing good
| and not disastrous plans. Not necessarily the best plan. This
| is the role of a query optimizer: do decently well and don't
| royally screw up. What makes optimizers to screw up is
| cardinality misestimations which leads to the wrong join
| order or wrong join types (nested loop join thinking that a
| subresult in a query plan is 3 rows where in reality it's
| 1M).
|
| Optimizing physical layout presents new challenges:
|
| - it takes time to build those layouts and consumes
| resources. nothing like adding a load to a database that's
| already struggling to keep up with the load :)
|
| - you don't know what you are going to break
|
| With all that said that's the future and the industry has to
| figure it out.
| falcolas wrote:
| They're often terrible at optimizing query plans, especially
| when there's no index, since the index provides data about
| the data (cardinality, for example) into the query optimizer.
| Beltalowda wrote:
| The problem is that indexes aren't free; an index can easily
| take up gigabytes of disk space, if not more. There are also
| performance considerations with respect to insert
| performance.
|
| What a "good" index is depends on the use case; for example
| for a webapp a 0.25 second query that's run on every pageload
| is extremely slow, and creating an index for that is almost
| certainly a good trade-off. On the hand, it's usually fine if
| some analytical query that only your finance people need once
| a month runs for a minute; in that case, an index is probably
| a poor trade-off. Or: in my app one query takes about ~5
| seconds for a rarely used feature; I could add an index to
| make it faster, but it would also eat up ~25G of disk space
| (last I checked, probably more now). The very small number of
| people using it can wait an extra few seconds.
|
| It's not too hard to automatically create indexes; I'd say
| it's almost easy. But these kind of judgements based on use
| case isn't something the database can do, and you're likely
| to end up with dozens of semi-useless indexes eating up your
| disk space and insert performance.
|
| This is probably one of those cases where automating things
| will lead to problems and confusion; it's probably better to
| spend time on tools to gain insight in what the database is
| doing, so a human can come along and do something that makes
| sense.
| maest wrote:
| > But these kind of judgements based on use case isn't
| something the database can do, and you're likely to end up
| with dozens of semi-useless indexes eating up your disk
| space and insert performance
|
| That is an empirical question and another comment points to
| this working just fine:
| https://news.ycombinator.com/item?id=31991469
|
| Even ignoring that, creating indexes is a matter of
| tradeoffs - it's not the index creation that's difficult
| but the decision on whether the tradeoff is worth it.
| Seeing this, possible issues arising from automatic index
| creation can be mitigated by allowing the admin to set
| parameters that dictate where the tradeoff should be made
| (as a naive example: "I'm okay with using 10Gb of storage
| if it results in a 20% improvement for P95 queries on this
| table").
|
| This is not an unreasonable idea and it sounds like it
| would greatly improve UX for devs working on median-sized
| DBs (people needing FAANG scale can manually tweak their
| DBs). Worst case, you can have a whitelist approach to
| automatic indexes, where the admin is shown index
| suggestions which require manual approval.
| iSnow wrote:
| >for example for a webapp a 0.25 second query that's run on
| every pageload is extremely slow, and creating an index for
| that is almost certainly a good trade-off. On the hand,
| it's usually fine if some analytical query that only your
| finance people need once a month runs for a minute; in that
| case, an index is probably a poor trade-off.
|
| But that should be automatable. If the DB sees frequent
| queries, it would create indexes, if those queries cease,
| it would drop them. If queries are infrequent, it would not
| create an index.
| abraae wrote:
| Would not want to be responsible for a database where
| some AI was vigorously creating and dropping indexes
| behind the scenes, just as I was trying to debug
| performance issues.
| cmgbhm wrote:
| The what to optimize then also becomes an optimization problem.
|
| Devs struggle to plan for read/write workloads. Making more
| indexes can then make the same inbound volume from the previous
| day require n more writes per row.
|
| You can still get surprised by query optimizations on relatively
| simple joins just changing query strategy when hitting tuning
| parameters.
|
| Making analyzers more visual and giving lots of suggestions (that
| could then be ignored) would b a good intermediate step.
| marcosdumay wrote:
| > You can still get surprised by query optimizations on
| relatively simple joins just changing query strategy when
| hitting tuning parameters.
|
| I imagine there is a large bias about caring or not about this
| depending on what DBMS you are used to.
|
| It just happens that on some DBMS you get "surprised" as "oh,
| shit, this problem again", and on others it usually comes as a
| surprise as "wait, I never thought about optimizer limits". The
| second set is much more prone to automating.
| rurban wrote:
| The one time I spent with an SAP sales guy in an elevator, he
| tried to convince me that SAP needs no manual indeces, because
| their database is auto indexing. Very convincing, because that
| might be the reason why SAP queries are so "fast". Like hours for
| a simple unindexed date range search.
| whoomp12342 wrote:
| what are sql statistics.
| ltbarcly3 wrote:
| The correct answer here is that any single query lacks the
| context of the entire suite of queries that will be performed
| against the database and their relative importance and
| sensitivity to performance.
|
| Indexes compete with each other and with table data for space in
| memory cache(s). They also require significant cpu and IO
| resources at write time to be kept up to date. Having too many
| indexes can lead to having them not being used (best case) or
| having them evict each other or more important data over and over
| from caches. In many cases it is better to not have one index
| that helps some specific query perform 50% better if it means
| another index is able to stay in memory that makes some other run
| in 1/500th as much time. It's often better to just scan data and
| have queries take an hour if they are for nightly reports rather
| than create the enormous multi-column indexes required to speed
| them up, since you don't care about the reporting queries taking
| an hour over night (at least in this hypothetical).
| smm11 wrote:
| The "modern" DB world reminds me of dentistry. Zero reason a
| database can't be plug and play right now.
|
| Dentistry wants to remain in the dark ages, and so does the
| database world. The rest of us just want something like the
| electrical (non-Texas) grid. Just works. When it doesn't, it
| comes back up, and all is fine.
| vajrabum wrote:
| What would dentistry that isn't in the dark ages do
| differently? Genuinely curious.
| WJW wrote:
| You can get a plug-and-play database experience at any of the
| dozens of companies that will host a database for you. You'll
| have to define your own tables and indexes of course, but the
| grid doesn't take care of the wires inside your house either.
|
| The electrical grid is kept up by the continuous effort of tens
| of thousands of employees across the country btw, it's hardly
| magic. I bet most people would be horrified if they realized
| how much manual planning is involved in keeping the electrical
| grid running.
| captainmuon wrote:
| A somewhat related question: Why can't you add an index to a
| simple in-memory list? It would be great if you could have a
| std::vector or Python list of plain old objects, and then tell it
| to add an index (using a hashtable for example) on one or two of
| the fields.
|
| I played with something like that in Python a couple of years
| ago, but never made it production-ready. I think in many simple
| cases it could replace a dedicated (SQL) database.
| layer8 wrote:
| Because, at least in C++ and Python that you mention, you can
| mutate (a field of) an element of the list without the list
| noticing, and hence without the index being updated. In the
| general case, you'd not only have to wrap the list, but also
| its elements, and wire the element wrappers up with the list.
| So it's better to have the application do that because it knows
| when the index needs updating for a given element (and its old
| & new value).
|
| The other reason is that most often you only need an index on
| one field, and don't need the elements to be in a particular
| order, so you just use a map/dict with the value of that field,
| instead of a list.
| nikita wrote:
| This is awesome idea! I'm sure there is an implementation
| somewhere.
| runekaagaard wrote:
| Here you go :) https://zodb.org/en/latest/
| ElectricalUnion wrote:
| > Why can't you add an index to a simple in-memory list?
|
| The index is already a "simple in-memory list", sorted. That's
| why it is expensive to mantain.
|
| > It would be great if you could have a std::vector or Python
| list of plain old objects
|
| Sort said Python list by the columns in your index.
|
| > and then tell it to add an index (using a hashtable for
| example) on one or two of the fields.
|
| Hashtable is another thing entirely, and designed for fast key
| retrieval at very high memory and CPU cost. If you can't afford
| a index you can't afford a Hashtable/dict either.
| ignoramous wrote:
| > _It would be great if you could have a std::vector or Python
| list of plain old objects, and then tell it to add an index
| (using a hashtable for example) on one or two of the fields._
|
| This is what a _LinkedHashMap_ (1 index) and _HashBasedTable_
| (2 indices) do in the jvm-land. Admittedly, there isn 't a
| convenience api to convert a given collection/vector to a
| map/table (but hey, if you're like me and write a lot of
| golang, then the current jvm-land status quo is already
| heaven!).
|
| https://guava.dev/releases/snapshot-jre/api/docs/com/google/...
| chakkepolja wrote:
| Boost has something called MultiIndex.
| tracnar wrote:
| This talk explored a bit that idea, you could have a (full)
| programming language which is more like SQL so you get good
| enough datastructures (and indexing- without manual tuning:
| https://www.youtube.com/watch?v=gC295d3V9gE
|
| I think Prolog (another commenter pointed it out) and datalog
| do this to some extend.
| mywittyname wrote:
| Honest question: how many applications are using in-memory
| lists that are large enough to warrant indexing but are also
| too small to put in a database?
|
| That's probably why such a library doesn't exist. Sorting &
| search is good enough for one field. Full scans aren't that bad
| for small/medium datasets. And databases aren't that difficult
| to integrate into an application.
|
| Also, pandas exists in the Python world.
| 6510 wrote:
| I had a different thought also when thinking of this back in the
| days. Compilers rolled into the application learning from the
| usage what parts are important and doing a recompile when it
| starts to seem useful. Code that is "never" used can be left on
| disk until it is needed. The clients can upload their strategy to
| a central server to configure default configs for novice, average
| and advanced users.
| nayuki wrote:
| > Why Can't Database Tables Index Themselves?
|
| I think the company Snowflake Computing was founded on this
| premise?
| simscitizen wrote:
| The obvious answer is that there are certain queries that cannot
| be made fast with normal indices. What index would you suggest
| for these queries? SELECT * FROM Foo WHERE Bar !=
| 42; SELECT * FROM Baz WHERE x > 42 ORDER BY y DESC LIMIT 10
|
| The usual solution is to constrain the query language in some way
| to prevent users from issuing queries that can't be satisfied
| efficiently using an index. If you are disciplined enough to do
| that then you can probably pretty easily suggest (or even
| automatically generate) indices for your users based on query
| patterns.
| mr_gibbins wrote:
| SELECT * FROM Foo WHERE Bar != 42; if Bar was an INT and had a
| high density (1/distinct values) then a columnstore index on
| Bar might perform faster than a per-row index. Worth trying out
| as it isn't certain and would depend on row cardinality.
|
| SELET * FROM Baz WHERE x > 42 ORDER BY y DESC LIMIT 10; a per-
| row index on x should in theory seek all values above 42 rather
| than perform an index/table scan, and the ordering and row
| limitation would be processed last i.e. in the SELECT. Again,
| not certain though.
|
| SQL Server already has the missing_index_stats DMVs which can
| suggest indexes based on previous query use, and generating
| frequency diagrams / histograms based on previous queries run
| is relatively straightforward and would contribute to any index
| suggestion engine.
| exabrial wrote:
| I don't think either of this would be difficult:
|
| For the first, if the 42 value is fixed, you can actually make
| that query quite fast. In MySQL you could create a functional
| index for the boolean condition and quickly find all the rows
| that don't meet that criteria. Basically all the rows are
| sorted at insert and your query would take ms (constant time).
|
| For the second, even if the 42 is not fixed, you could create a
| composite index for X, and Y DESC and get very acceptable
| results (log time).
| simscitizen wrote:
| The 42 was meant to be a placeholder in the examples. Imagine
| it is a ? and bound at runtime instead.
|
| In the second query, a composite index over (x, y) basically
| does the same job as an index over just x. The general query
| plan is to iterate over all values > x and keep the 10
| smallest values around in memory to satisfy the ORDER BY.
|
| The point is that there are many queries that seem
| deceptively simple but are actually extremely hard or even
| impossible to automatically index. Those queries which result
| in table scans, large index scans, and large sorts can easily
| dominate all the other queries which are easily indexable in
| terms of resource usage.
| exabrial wrote:
| Gotcha. Yeah, you'd definitely be constrained to log time,
| though with proper bucket sizing it'd be manageable if the
| values were more bounded. For large datasets, there's just
| not a real good way to make this fast if you need an
| _exact_ answer. If you can deal with a probabilistic answer
| there's faster methods.
| simscitizen wrote:
| So basically I realize there are ways to generate index
| suggestions (or even generate indices automatically) by
| observing query patterns, keeping stats on cardinality of
| various columns, etc. We actually did that at Parse; we
| would observe query patterns and generate indices
| (including compound indices) at runtime that seemed to
| benefit the application's query pattern. There wasn't an
| option for this, it was just enabled by default.
|
| However we ran into the problem that users still made
| many queries which were essentially impossible to index.
| On many of the DB nodes, the resources used by these
| unindexable queries dominated the resources used by all
| of the easily indexed queries.
|
| I left the experience thinking that there really wasn't
| any good solution to the problem for general users other
| than making the query language force users to only make
| queries that can be obviously satisfied by common index
| types.
| davidelettieri wrote:
| On azure, SQL database advisor suggest (and automatically apply
| if you want) indexes based on the workload of the db. If you
| decide to apply the index, it evaluates for a while if it is
| working as expected and if not it rollbacks the index creation
|
| https://azure.microsoft.com/it-it/blog/simplify-performance-...
| chasil wrote:
| I have only used it on Oracle 10g, but dbms_sqltune is a PL/SQL
| package that implements the SQL Tuning Advisor.
|
| I wrote some PHP for my users to find their queries and run the
| analyzer, and it will suggest new indexes among other
| performance-enhancing options.
|
| There is also this SQLite index suggestion tool on the front
| page today...
|
| https://news.ycombinator.com/item?id=31991598
| RajT88 wrote:
| Azure Cosmos also has a default index which you can override
| for better performance.
| vyrotek wrote:
| This is a great feature. We manually added the indexes it
| recommended for a while but eventually found ourselves always
| adding the suggestions so we turned on the automatic creation.
| bingaling wrote:
| Some analysis of the Azure / Sql Server 2017+ automatic index
| tuning:
|
| https://www.brentozar.com/archive/2019/02/what-does-azure-sq...
| [deleted]
| gangulo96 wrote:
| Check out this project from CMU, one of the goals is to use ML-
| driven auto-indexing. https://noise.page/about/
| tshanmu wrote:
| this is from the group run by Prof Andy Pavlo who is the
| founder of ottertune (referenced in other comments)
| ape4 wrote:
| Being obvious, if a field is called "index" (or similar) and
| doesn't have an index on it maybe the database could default to
| making it one.
| ElectricalUnion wrote:
| "Obvious" for you, maybe for a general AI, but not to the DBMS.
|
| What if such field is a blob with a uncompressible 12mb blob of
| a image of a index card?
|
| What if indexing them would waste several TBs of RAM? That is
| not a good default.
|
| On a more realistic scenario, it is reasonably rare that you
| will only ever search using the PK, and no other constraint, so
| in a way is a wasted default index.
| ape4 wrote:
| Good point, make that only apply to fixed length chars (not
| varchars). I think JOINs use indexes which are often done on
| the primary key.
| https://www.mssqltips.com/sqlservertutorial/3207/make-
| sure-a...
| nwsm wrote:
| Article title could use a (2006) @mods @dang
| kthejoker2 wrote:
| @dang Mark this from 2006.
|
| Most modern databases do auto optimization, stats, and index
| tuning.
| Wowfunhappy wrote:
| Friendly heads up, @dang, @mods, and everything in that
| category doesn't do anything.
| dredmorbius wrote:
| Correct. Best option is to email mods at hn@ycombinator.com
|
| Though someone might also see the mention in a thread.
| There's no specific notification value in tagging mods or
| admins howevers.
| ignoramous wrote:
| And techniques like learned-indices [0], optimizers like
| ottertune [1] / dynimize [2], and alt-views like materialize.io
| and readyset.io exist.
|
| [0] https://news.ycombinator.com/item?id=25899286
|
| [1] https://news.ycombinator.com/item?id=28868382
|
| [2] https://news.ycombinator.com/item?id=17025627
| triska wrote:
| In modern Prolog systems, this is in fact done: When looking up
| clauses from the database, the Prolog system builds an index as
| needed based on which arguments of the query are instantiated.
| This is called Just-In-Time (JIT) indexing.
|
| Modern Prolog systems also perform multi-argument and deep
| indexing. In older Prolog systems, you sometimes had to declare
| explicitly on which arguments the system should index, analogous
| to those database systems where you have to declare indices
| manually.
| ginko wrote:
| It sometimes feel like me learning Prolog completely ruined me
| for SQL. Doing even simple things seems ridiculously hard with
| SQL.
| kristiandupont wrote:
| You just piqued my interest in Prolog more than any pitch I
| had heard until now. Would you care to elaborate? How is it
| nicer than SQL?
| vkazanov wrote:
| Unlike SQL, Prolog's core language is both simple and
| composable: about 200 pages. Sql is just a bunch of special
| cases and unfortunate historical accidents.
|
| It's a lot like... Php4 in comparison with StandardML.
|
| And, sadly, both SML and Prolog are fading away, while SQL
| (and php) are here to stay.
| thesz wrote:
| For me it is the other way around.
|
| Prolog allows effects on its database while executing the
| "query" - you can add or remove facts from database. SQL
| prohibits that, separating queries and data manipulation
| statements.
|
| And, of course there are things like list comprehension in
| Haskell and other languages which are more or less the same
| as SQL but definitely more succinct.
| gota wrote:
| Hm, the language does not enforce it, but you _can_
| separate things neatly for sure
|
| I may have missed something, though, are you thinking of
| something other than asserting and retracting?
| wizofaus wrote:
| Can you syntactically write a SELECT query that also does
| an UPDATE/INSERT/DELETE? And Bobby Tables doesn't count -
| using a ; clearly indicates the start of a new query/
| statement.
| ElectricalUnion wrote:
| SELECT drop_table_procedure() ;
| samatman wrote:
| No, but you can write an UPDATE which uses a SELECT in
| all of SET, FROM, and WHERE, which broadly speaking will
| do the same thing.
|
| The difficulty in figuring out all this syntactic
| weirdness is probably overstated for small queries, but
| it's there. Most attempts to write a better SQL I've seen
| succeed in narrow terms, but if the goal is to replace
| SQL rather than write a better syntax which (almost) no
| one uses, that's not enough.
| wizofaus wrote:
| That's what I had always assumed - i.e. if the first
| keyword is SELECT it can't possibly modify the data,
| which does match the OP's claim that SQL separates
| queries from data manipulation commands.
| triska wrote:
| As I see it, the fact that this (legitimate!) question
| even arises is saying a lot about SQL syntax:
|
| _Can we syntactically write a SELECT query that also
| does an UPDATE /INSERT/DELETE?_ It may be possible, or it
| may be not possible, and to find out which it is, one has
| to have a good grasp of a quite complex syntax
| specification.
|
| For comparison, in Prolog, queries have a very uniform
| and simple syntax, and a small set of predicates is used
| to modify the database. You can find out whether a query
| performs any of these operations by _meta-interpreting_
| the query. It is easy to write a meta-interpreter for
| Prolog queries, and to allow only calls of predicates
| that you _know_ do not affect the database. In
| comparison, it is extremely hard to analyze and interpret
| SQL queries, also because the syntax of SQL is so complex
| that we cannot easily answer such basic questions about
| the syntax.
| dragonwriter wrote:
| > SQL prohibits that, separating queries and data
| manipulation statements.
|
| With the exception of the case where the set of rows you
| want to return (a projection of) are exactly the same ones
| being modified.
|
| IIRC, you can hack around this about with JOIN in the FROM
| clause of the main statement even if it is only of
| substantive use in the RETURNING clause, to get what
| amounts to arbitrary data modifying queries.
| triska wrote:
| In his seminal paper _A Relational Model of Data for Large
| Shared Data Banks_ , Codd explicitly mentions _first-order
| predicate calculus_ as a suitable formalism for the sub-
| language. Quoting from
| https://dl.acm.org/doi/pdf/10.1145/362384.362685:
|
| _" The adoption of a relational model of data, as described
| above, permits the development of a universal data sub-
| language based on an applied predicate calculus. A first-
| order predicate calculus suffices if the collection of
| relationships is in normal form."_
|
| One great advantage of using Prolog is that queries
| themselves are so readily amenable to analysis and reasoning
| with the same mechanism, and it becomes very easy to optimize
| them by reordering goals.
| Yoric wrote:
| I've only ever seen/used Prolog with a fixed set of facts
| (axioms? I forgot how they are called). Are there modern Prolog
| systems with on-disk storage that could replace a DBMS?
___________________________________________________________________
(page generated 2022-07-05 23:01 UTC)