sql - Effective filtering results after 'limit by' clause (clickhouse) - Stack Overflow

admin管理员组

文章数量:1279145

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

Playground with query

本文标签： sqlEffective filtering results after 39limit by39 clause (clickhouse)Stack Overflow