# Why is SELECT * much faster than selecting all columns (in a different column order) by name?

On a table with columns a,b,c,d,e,f,g,h,i,j,k I get:

``````select * from misty order by a limit 25;
Time: 302.068 ms
``````

And:

``````select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25;
Time: 1258.451 ms
``````

Is there a way to make the select by column as fast?

Update:

No index on a table, newly created one

Here is the EXPLAIN ANALYZE, does not seem too helpful:

``````explain analyze select * from misty order by a limit 25;

Limit  (cost=43994.40..43994.46 rows=25 width=190) (actual time=404.958..404.971 rows=25 loops=1)
->  Sort  (cost=43994.40..45731.11 rows=694686 width=190) (actual time=404.957..404.963 rows=25 loops=1)
Sort Key: a
Sort Method: top-N heapsort  Memory: 28kB
->  Seq Scan on misty  (cost=0.00..24390.86 rows=694686 width=190) (actual time=0.013..170.945 rows=694686 loops=1)
Total runtime: 405.019 ms
(6 rows)
``````

And:

``````explain analyze select c,b,j,k,a,d,i,g,f,e,h from misty order by a limit 25;

Limit  (cost=43994.40..43994.46 rows=25 width=190) (actual time=1371.735..1371.745 rows=25 loops=1)
->  Sort  (cost=43994.40..45731.11 rows=694686 width=190) (actual time=1371.733..1371.736 rows=25 loops=1)
Sort Key: a
Sort Method: top-N heapsort  Memory: 28kB
->  Seq Scan on misty  (cost=0.00..24390.86 rows=694686 width=190) (actual time=0.015..516.355 rows=694686 loops=1)
Total runtime: 1371.797 ms
(6 rows)
``````
• Is column a indexed? Can you post explain analyze? – user_0 May 25 '15 at 6:37
• You need to be careful making two selects in a row and comparing the times. The data in cache on the second query may account for the difference in the times. – Walter Mitty May 25 '15 at 6:39
• I'm seeing differences also, though not as pronounced. My table has rows=514431 width=215, and I get approx 1.5s for the `select *` case and approx 2.2s for the select with columns listed in a different order. – Colin 't Hart May 25 '15 at 9:09
• If I list all columns in the same order as defined in the table, I get approximately the same times as if I `select *`. – Colin 't Hart May 25 '15 at 9:11
• The title is misleading. The question really is why the duration of a sort depends on the order of the output columns. – Daniel Vérité May 25 '15 at 13:55