Performance difference in accessing differrent columns in a Postgres Table

Question

Created a table with 200 bigint column, 200 varchar column. (Postgres 10.4)

create table i200c200 ( pk bigint primary key, int1 bigint, int2 bigint,....., int200 bigint, char1 varchar(255),......, char200 varchar(255)) ;

Inserted values only in pk,int1,int200 columns with some random data ( from generate series) and remaining columns are all null. The table has 1000000 rows.

I found performance variance between accessing int1 and int200 column which is quite large.

Reports from pg_stat_statements:

                  query                  | total_time | min_time | max_time | mean_time |    stddev_time     
-----------------------------------------+------------+----------+----------+-----------+--------------------
 select pk,int1 from i200c200 limit 200  |       0.65 |    0.102 |    0.138 |      0.13 | 0.0140142784330839
 select pk,int199 from i200c200 limit $1 |      1.207 |     0.18 |    0.332 |    0.2414 | 0.0500583659341773 
 select pk,int200 from i200c200 limit 200|       1.67 |    0.215 |    0.434 |     0.334 | 0.0697825193010399

Explain Analyse:

explain analyse select pk,int1 from i200c200 limit 1000;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..23.33 rows=1000 width=16) (actual time=0.014..0.390 rows=1000 loops=1)
   ->  Seq Scan on i200c200  (cost=0.00..23334.00 rows=1000000 width=16) (actual time=0.013..0.268 rows=1000 loops=1)
 Planning time: 0.066 ms
 Execution time: 0.475 ms

 explain analyse select pk,int200 from i200c200 limit 1000;
                                                      QUERY PLAN                                                      
----------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..23.33 rows=1000 width=16) (actual time=0.012..1.001 rows=1000 loops=1)
   ->  Seq Scan on i200c200  (cost=0.00..23334.00 rows=1000000 width=16) (actual time=0.011..0.894 rows=1000 loops=1)
 Planning time: 0.049 ms
 Execution time: 1.067 ms

I am curious in getting this postgres behaviour and its internals.

Answer 1

The way the data is stored, PostreSQL doesn't know what offset to go to look at for the data for int200 column on any given row, without knowing the sizes of all previous columns for that row. So it has to loop through the null bitmap (at a minimum) of all earlier columns before it can know that most of them have zero size.

There is an optimization for cases where you are selecting a single column and that column is found to be null via use of the null bitmap. But you are selecting two columns not just the column int199, so you don't trigger this optimization. It is unlikely an optimization would be added for selecting multiple, widely separated columns. You would need some heuristic to decide what makes the columns widely separated, and then everyone would pay the cost for running that heuristic, even the vast majority of the cases that can't benefit from it. If you were willing to get your hands dirty with a bit of hacking, perhaps you could convince us otherwise. But monkeying around with the internals has a steep learning curve.

If you have a practical use case for making this situation faster, perhaps you should check out Citus Data's column store extension.