Why select max partitioned column is much slower than indexed one?

I have a big partitioned table (300+M rows, 70 active partitions). The table has clustered PK of (K0, K1) and it's partitioned on column K1. And K1 has very low selectivity.

However the following query takes a half minute and the scan count is 170 and logical read is 603K.

select max(K1) from table

However the similar query on on non-partitioned table with index on K1 takes no time and scan count is 1 and logical read is 5.

Should a none partitioned index be created on K1?

Edit:

Plan text for partitioned table without index on K1. With clustered PK on K0,K1, before create an index on C1.

|--Stream Aggregate(DEFINE:([Expr1004]=MAX([partialagg1005])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1005]=MAX([DB1].[dbo].[table1].[K1])))
|--Clustered Index Scan(OBJECT:([DB1].[dbo].[table1].[PK_dbo_tabl1]))

Plan text for non-partitioned table with index on K1.

|--Stream Aggregate(DEFINE:([Expr1003]=MAX([DB1].[dbo].[table2].[K1])))
|--Top(TOP EXPRESSION:((1)))
|--Index Scan(OBJECT:([Db1].[dbo].[table2].[idx_K1]), ORDERED BACKWARD)

Plan text for partitioned table with clustered PK on K0,K1, and index on C1.

|--Stream Aggregate(DEFINE:([Expr1004]=MAX([partialagg1005])))
|--Parallelism(Gather Streams)
|--Stream Aggregate(DEFINE:([partialagg1005]=MAX([db1].[dbo].[table1].[K1])))
|--Index Scan(OBJECT:([db1].[dbo].[table1].[IX_C1]))

Think I mis-understood/mis-read the question initially.


The table is partitioned on column K1. However the following query
takes a half minute and the scan count is 170 and logical read is
603K.

To satisfy the MAX(k1) query in this case requires a clustered index scan of each of the 70 partitions.


However the similar query on on non-partitioned table with index on K1
takes no time and scan count is 1 and logical read is 5.

With an index defined on K1, an ORDERED BACKWARD scan of the index is chosen, which probably translates to 4 pages read from the non-leaf index pages plus 1 for the leaf level.

StackExchange Database Administrators Q#13034, answer score: 4