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.

  • jasen=# create table i200c200 ( pk bigint primary key, int(1..200) bigint , char(1..200) varchar(255)) ; ERROR: syntax error at or near "(" LINE 1: create table i200c200 ( pk bigint primary key, int(1..200) b... – Jasen Jul 28 '18 at 8:13
  • @Jasen I think int(1..200) bigint is just shorthand for int1 bigint, int2 bigint, … – Jack Douglas Jul 28 '18 at 8:18
  • @JackDouglas you are correct.. – Dinesh Kumar Jul 28 '18 at 8:25
  • @JackDouglas.I have the tried the same query with int199 column which is null in all rows,it is still performance variant.Since,postgres doesn't store null values in data instead it store in null bit map,there should not be this variation(because i'm having data only for pk,int1,int200).I am wondering that this null bit map lookup is slowing down this , because each row in my table is having a null bit map of size (408 bits).As newbie I am wondering whether this null bit map lookup for non-earlier column is taking too much time (for scanning the null bit map itself).Am i thinking in right way? – Dinesh Kumar Jul 28 '18 at 10:44
  • Suggest you post this question to the Postgres developers on the pghackers mailing list and report back here in the form of an answer. – Colin 't Hart Jul 28 '18 at 17:00

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.

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.