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 documentationSEDE 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.