3 ms·
> We just used varchar here to prove the point. The same issues will happen with other data types – the problem is simply more apparent with varchar as it is mo
by masklinn 4y ago
> We just used varchar here to prove the point. The same issues will happen with other data types – the problem is simply more apparent with varchar as it is more complicated internally than, say, integer.
That’s not entirely honest. The problem is caused by the varchar (or any other variable-size column).
With fixed-size columns, postgres doesn’t need to look at the row to determine the offset of a value, it can just loop through the pg_attributes (the column definitions) and look at the nulls bitmap in the row header to skip null values. It still needs some computation (to sum the sizes of the previous columns iff they’re non-null) but that’s a lot more performance-friendly than also needing to go hit and the data tuple and decode the varlen metadata of every varlen field.
- deleted 4y ago[deleted]
- jabart 4y agoAlso pretty sure almost every database with variable width columns has this issue. An old MS SQL Server optimization was to use chars and not have nulls since that drops a few bits too. That way your table was easy to find row 500 on disk. This was back when SCSI and IDE drives were common though. Doesn't indexes fix this too? You can index on column 100 which puts in on disk as Column100|PrimaryKey and now it's a really narrow table. Like yeah you shouldn't have a table with 1,500 columns but sometimes you really do need it. Edit Flipped the index layout.
- masklinn 4y ago> Doesn't indexes fix this too? If your query is such that an index-only scan will fulfil it then yeah, it should.
- sokoloff 4y agoI haven’t looked at the pg source, but wouldn’t an index on Column100 be laid out on disk as Column100|PrimaryKey rather than PrimaryKey|Column100?
- jabart 4y agoOops yeah, got that flipped.