3 ms·

I know it matters but I really wish it didn’t. When I’m developing I simply don’t have time to think about the nuances of how I should order columns in a table,

by xwdv 4y ago

I know it matters but I really wish it didn’t. When I’m developing I simply don’t have time to think about the nuances of how I should order columns in a table, this really needs to be abstracted out and optimized behind the scenes.

minusf 4y ago
as with many things "it depends" -- it matters "after a certain point". will your app make it to that point? let's hope it does, and then you'll be happy to learn about some more advanced optimisations like this. it's a good problem to have. grow with your app like a gardener :} dont learn how to make skyscrapers when you are building houses...
dspillett 4y ago
They were showing a pathological case where there are a very large number of columns and table scanning is the only option the optimiser has, so in most cases it won't matter significantly (removing the need to scan at all will fix that, by adding indexes or altering the query to make filtering and joining clauses sargable). This is more an interesting experiment than something you generally need to worry about, and if you do need to worry about it there are other things you may need to fix first (do you really need 1,500 columns on that table, is there not a design that is orders of magnitude more efficient generally thus making this issue measurable but insignificant?). PG could be refactored to make these cases much more efficient, but the developer (and QA) time required would be much better spent elsewhere and the changes needed may impact the performance of other operations that occur far more regularly in both good and bad designs.
AdrianB1 4y ago
I saw in such cases a 2 step approach, the first part the developers write their code and later on the database administrators perf tune it. It worked well for my teams in the past 5-6 years, YMMV.

3 ms·

I know it matters but I really wish it didn’t. When I’m developing I simply don’t have time to think about the nuances of how I should order columns in a table,

by xwdv 4y ago

minusf 4y ago
as with many things "it depends" -- it matters "after a certain point". will your app make it to that point? let's hope it does, and then you'll be happy to learn about some more advanced optimisations like this. it's a good problem to have. grow with your app like a gardener :} dont learn how to make skyscrapers when you are building houses...
dspillett 4y ago
They were showing a pathological case where there are a very large number of columns and table scanning is the only option the optimiser has, so in most cases it won't matter significantly (removing the need to scan at all will fix that, by adding indexes or altering the query to make filtering and joining clauses sargable). This is more an interesting experiment than something you generally need to worry about, and if you do need to worry about it there are other things you may need to fix first (do you really need 1,500 columns on that table, is there not a design that is orders of magnitude more efficient generally thus making this issue measurable but insignificant?). PG could be refactored to make these cases much more efficient, but the developer (and QA) time required would be much better spent elsewhere and the changes needed may impact the performance of other operations that occur far more regularly in both good and bad designs.
AdrianB1 4y ago
I saw in such cases a 2 step approach, the first part the developers write their code and later on the database administrators perf tune it. It worked well for my teams in the past 5-6 years, YMMV.