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.0140142784330839select 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 msExecution 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 msExecution time: 1.067 ms
I am curious in getting this postgres behaviour and its internals.
