We are using a clustered columnstore index table with ~50M records and experiencing big performance degradation when running on GCP cloud sql vs. running locally using the same db schema and data (just exported and imported a bak file).
Using the below query, we found that the execution plan on GCP cloud sql (https://www.brentozar.com/pastetheplan/?id=ByexjqpCF) doesn't use the projectId predicate on the index scan and instead applies it only in a further filter step. When running locally (https://www.brentozar.com/pastetheplan/?id=rJLO59pRK) the predicate is pushed into the index scan resulting in much fewer rows to scan and better performance.
What could be the reason for such difference?
SELECT YEAR(ReferenceDate) RefDateYear, MONTH(ReferenceDate) RefDateMonth,sum(diffsum) DiffSum into #res FROM Journal jp WHERE jp.projectId='582b02e2-add0-4b50-94f7-4e7e07497cf6' AND ReferenceDate < '20220101' AND batch != 9998 GROUP BY YEAR(ReferenceDate), MONTH(ReferenceDate)
See also the table schema:
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Journal]( [id] [int] IDENTITY(1,1) NOT NULL, [projectId] [uniqueidentifier] NOT NULL, [diffSum] [money] NOT NULL, [batch] [int] NOT NULL, [referenceDate] [datetime2](7) NOT NULL, ... ) ON [PRIMARY] GO ALTER TABLE [dbo].[Journal] ADD CONSTRAINT [Journal_pkey] PRIMARY KEY NONCLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO CREATE CLUSTERED COLUMNSTORE INDEX [CCI_journal] ON [dbo].[Journal] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY] GO