Difference between "order by foo, bar" and "order by (foo, bar)" (with parenthesis)

user_62187 0
Viewed 60

I'm interested in Postgres, but I suppose the answer may be universally the same. I'm curious what, if anything, is different between these two queries, where foo is a numeric (bigint) and bar is a string (varchar) and together they make up the primary key (foo, bar).

select * from my_table order by foo, bar for update

select * from my_table order by (foo, bar) for update

There is a difference in the explain plan (see below), but it's not obvious to me if it actually changes the lock order, which is what I'm really interested in. I'm battling some occassional deadlocks and the one thing I've noticed is inconsistent use between the two different sorting rules. Perhaps this could be the cause?

LockRows  (cost=83.37..98.37 rows=1200 width=78)
  ->  Sort  (cost=83.37..86.37 rows=1200 width=78)
        Sort Key: (ROW(foo, bar))
        ->  Seq Scan on my_table  (cost=0.00..22.00 rows=1200 width=78)

LockRows  (cost=0.15..78.15 rows=1200 width=46)
  ->  Index Scan using my_table_pkey on my_table  (cost=0.15..66.15 rows=1200 width=46)
postgresql
1 Answers

(foo, bar) is a single column of an anonymous record type (also referred to as a row constructor).

As there is no index for that (single) column Postgres has to read the entire table ("Seq Scan") to be able to sort by the expression. It's similar (but not identical) to order by foo||bar.

When selecting the two columns that are apparently part of the primary key index, Postgres can look up the rows in index order and therefor skip the sorting.

You can see the difference if you run select (foo, bar) from ... the output only has a single column.

but it's not obvious to me if it actually changes the lock order

The sort order is not affected but performance is (as you can see in the execution plan)

Putting multiple columns between parentheses is wrong more often than not in Postgres (and pretty much useless in other databases)

user_3303150
Related