I have a SQL 2016 table that looks as follows:
I have a columnstore clustered index on the table and no other indexes. I'm executing the following query:
Select * From dbo.PanelWorkflow Where ReadTime Between '4/1/2016' And '4/5/2016' And Lineage = 'PBG11A' And ProcessNumber = 5400
The query plan looks as follows:
This table has 1.1B rows. The query plan shows a scan and the query takes about 1 minute to execute. ~21K rows are returned. Is this what I can expect from columnstore clustered indexes? Will a scan always be done (assuming no other indexes on the table)? Is this the sort of performance I can expect (acceptable but not great)?
I set Statistics IO on and got the following output:
Table 'PanelWorkflow'. Scan count 2, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 9733431, lob physical reads 195, lob read-ahead reads 13045645. Table 'PanelWorkflow'. Segment reads 955, segment skipped 336.
Finally, the query above is strictly a test query to get a feel for how CSIs perform and operate. This is not a production level query.