3 ms·
Why can’t the engine determine a good packing for the field types? I’ve always been under the impression that rdbms engines do this, that my CREATE TABLE comman
by jagged-chisel 4y ago
Why can’t the engine determine a good packing for the field types? I’ve always been under the impression that rdbms engines do this, that my CREATE TABLE command doesn’t dictate column order - why would it? It what case does the column order matter to the user? During SELECT, I expect the returned fields to come in the order the query specifies, but that’s usually different from how the table was created.
- klysm 4y agoThere is an argument for you to be able to decide the packing yourself. Let’s say you have a few variable length fields but you know one of them will be accessed much more frequently. You can put that one first. The database won’t know that information.
- gleenn 4y agoSeems like it couldn't hurt to do the initial schema load in an optimal way, and if you add and drop columns to oblivion then that sucks but most people are add-only for databases of any sufficient size in general.
- jagged-chisel 4y agoI can see it being optional.
- dietr1ch 4y agoI just want to specify an order that makes reading easier, not one that would work nicely, I'm not an optimizing compiler, don't make me do that work. There's sometimes need for control in performance-critical things, but that needs an escape hatch instead of providing everyone with an implicit tool they don't even know they have. The same applies to struct packing in I guess most compiled languages and using the order to define ABIs.
- fdr 4y agoit's been occasionally suggested. There are limits: for example, maybe during initial creation, sure, it can pack for you, but as you start adding and dropping fields it gets more complex, and databases of consequence tend to have long lives where the returns on complication to get a better result in the initial schema load look a bit more marginal. If you think a bit about how much more complex the cataloging of pg_attributes and the storage code needs to be to support multiple arrangements of attributes in one table -- feasibly more complicated, but definitely a lot more complicated -- you can get an idea why it isn't done.
- akira2501 4y agoWhat order of columns should SELECT * return?
- jagged-chisel 4y agoRandom. Unpredictable. Whatever the engine feels like doing today. If my client code can’t handle that, then I should specify the list in the order I want.
- jeltz 4y agoBecause nobody has written that code. There have been some work on this but nobody has finished it since it requires a quite large refactor of PostgreSQL.