12

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
  • 1
    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
  • 1
    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
  • 2
    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
12

This was posted to pgsql-hackers mailing list and I tried to answer in brief there. It seems if the target list (specified columns) matches the tuple descriptor of the relation exactly, that is, both in number of columns and order, then the underlying scan can return a tuple that's directly consumable by the enclosing Sort node. On the other hand, if the target list does not match (either in order or the number of specified columns), the scan returns a form of the tuples that requires Sort's data preparation step to perform extra work (convert from an internal tuple format to the format directly consumable by the sorting code).

By the way, '*' is internally transformed into a list that (intuitively) matches the relation's tuple descriptor.

EDIT: If you look at your latter EXPLAIN ANALYZE's Seq Scan's actual times, you can see it's more than former's. That happened because the scan performed an additional step of projection (that is, converting heap tuple into an internal values[], nulls[] format). And because that happened, the upper Sort node had to do extra work in its data initialization, that of converting it back to the tuple format that the actual sorting step understands. That's evident from the Sort's startup cost. That doesn't happen in the former case. That is, both the scan returns the tuple as it is and sort's initialization step simply copies it.

  • @Colin'tHart, hope that makes sense. – amitlan May 25 '15 at 14:07
  • Yes. I would've hoped it would be possible to skip that step or make it shorter by using some "pointer shuffling", but that's a discussion for pgsql-hackers. – Colin 't Hart May 25 '15 at 14:10
  • There may be some improvements on the horizon with the recent revival of logical column ordering work. – amitlan May 25 '15 at 14:15
  • I was already thinking about that and hoping so! – Colin 't Hart May 25 '15 at 14:16
  • dear sir, if I only need some columns instead of all, which will be faster? select * or select some_of_columns? Thanks a lot. – sgon00 Aug 4 '19 at 9:23

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.