Reputation: 1
I have simple 'checkins' table.
CREATE TABLE checkins
(
`name` String,
`ddate` date,
`status` String
)
Engine = Memory;
And i am trying to get person last status if it equals to 'REJECTED'.
select * from
(
select * from checkins order by name, ddate desc limit 1 by name
) where status='REJECTED' order by name;
I want to get ordered by name list results. So outer query have 'order by name'. At the same time inner query also ordered by 'name, ddate desc'.
I am expecting, that there is one sorting pass needed, because inner already delivering sorted results to outer query. But, unfortunately, plan of query have double sort pass.
Expression (Project names)
Sorting (Sorting for ORDER BY). -- SECOND SORT PASS
Expression ((Before ORDER BY + Projection))
Filter ((WHERE + (Change column names to column identifiers + Project names)))
LimitBy
Expression (Before LIMIT BY)
Sorting (Sorting for ORDER BY) -- FIRST SORT PASS
Expression ((Before ORDER BY + (Projection + Change column names to column identifiers)))
ReadFromMemoryStorage
Is it possible to avoid double sorting here or any improvements for performance ?
Any suggesions welcomes!
More verbose plan of query ('explain actions=1,header=1...'):
Expression (Project names)
Header: name String
ddate Date
status String
Actions: INPUT : 0 -> __table1.name String : 0
INPUT : 1 -> __table1.ddate Date : 1
INPUT : 2 -> __table1.status String : 2
ALIAS __table1.name :: 0 -> name String : 3
ALIAS __table1.ddate :: 1 -> ddate Date : 0
ALIAS __table1.status :: 2 -> status String : 1
Positions: 3 0 1
Sorting (Sorting for ORDER BY)
Header: __table1.name String
__table1.ddate Date
__table1.status String
Prefix sort description: __table1.name ASC
Result sort description: __table1.name ASC
Expression ((Before ORDER BY + Projection))
Header: __table1.name String
__table1.ddate Date
__table1.status String
Actions: INPUT :: 0 -> __table1.name String : 0
INPUT :: 1 -> __table1.ddate Date : 1
INPUT :: 2 -> __table1.status String : 2
Positions: 0 1 2
Filter ((WHERE + (Change column names to column identifiers + Project names)))
Header: __table1.name String
__table1.ddate Date
__table1.status String
Filter column: equals(__table1.status, \'REJECTED\'_String) (removed)
Actions: INPUT : 0 -> __table2.name String : 0
INPUT : 1 -> __table2.ddate Date : 1
INPUT : 2 -> __table2.status String : 2
COLUMN Const(String) -> \'REJECTED\'_String String : 3
ALIAS __table2.name :: 0 -> name String : 4
ALIAS __table2.ddate :: 1 -> ddate Date : 0
ALIAS __table2.status : 2 -> status String : 1
FUNCTION equals(__table2.status :: 2, \'REJECTED\'_String :: 3) -> equals(__table1.status, \'REJECTED\'_String) UInt8 : 5
ALIAS name :: 4 -> __table1.name String : 3
ALIAS ddate :: 0 -> __table1.ddate Date : 4
ALIAS status :: 1 -> __table1.status String : 0
Positions: 5 3 4 0
LimitBy
Header: __table2.name String
__table2.ddate Date
__table2.status String
Columns: __table2.name
Length 1
Offset 0
Expression (Before LIMIT BY)
Header: __table2.name String
__table2.ddate Date
__table2.status String
Actions: INPUT :: 0 -> __table2.name String : 0
INPUT :: 1 -> __table2.ddate Date : 1
INPUT :: 2 -> __table2.status String : 2
Positions: 0 1 2
Sorting (Sorting for ORDER BY)
Header: __table2.name String
__table2.ddate Date
__table2.status String
Sort description: __table2.name ASC, __table2.ddate DESC
Expression ((Before ORDER BY + (Projection + Change column names to column identifiers)))
Header: __table2.name String
__table2.ddate Date
__table2.status String
Actions: INPUT : 0 -> name String : 0
INPUT : 1 -> ddate Date : 1
INPUT : 2 -> status String : 2
ALIAS name :: 0 -> __table2.name String : 3
ALIAS ddate :: 1 -> __table2.ddate Date : 0
ALIAS status :: 2 -> __table2.status String : 1
Positions: 3 0 1
ReadFromMemoryStorage
Header: name String
ddate Date
status String
Upvotes: 0
Views: 54