I'm writing an application that uses the Stack Exchange Data Dump. I've imported the most recent dump into SQL Server 2014 on my local development machine.
I wrote a query that runs very slowly on my machine, but runs reasonably fast on SEDE. I have some indices on my tables in my dev database, but I'd be interested in knowing what indices SEDE uses, so that I can also use them in my dev database. Is it possible to get a detailed list, like the detailed SEDE documentation?
This is the query that I've been running on both my dev machine and on SEDE,
SELECT a.Id FROM Posts q INNER JOIN Posts a ON a.ParentId = q.Id INNER JOIN PostTags pt ON pt.PostId = q.Id WHERE pt.TagId IN ( SELECT DISTINCT TagId FROM Posts q INNER JOIN Posts a ON a.ParentId = q.Id INNER JOIN PostTags pt ON pt.PostId = q.Id WHERE a.OwnerUserId = ##UserId:int## )
These are my indices that I've defined for my dev database,
CREATE INDEX IDX__Users__CreationDate ON Users (CreationDate); CREATE INDEX IDX__Posts__PostTypeId ON Posts (PostTypeId); CREATE INDEX IDX__Posts__CreationDate ON Posts (CreationDate); CREATE INDEX IDX__Posts__OwnerUserId ON Posts (OwnerUserId); CREATE INDEX IDX__Posts__ParentId ON Posts (ParentId); CREATE INDEX IDX__Tags__TagName ON Tags (TagName); CREATE INDEX IDX__PostTags__TagId_PostId ON PostTags (TagId, PostId);
I examined the query execution plans both on my dev database and on SEDE, but even on SEDE it's not entirely clear to me what kind of indices are being used.