What are the indices used in SEDE?

Question

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.

Answer 1

Here's a script out of the indexes on the live SEDE instance. This isn't how they are created, but it's a script of what's currently applied after an import, hope it helps!

CREATE UNIQUE CLUSTERED INDEX [UIX_Badges_Id] ON [dbo].[Badges](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_CloseAsOffTopicReasonTypes_Id] ON [dbo].[CloseAsOffTopicReasonTypes](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_CloseReasonTypes_Id] ON [dbo].[CloseReasonTypes](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_Comments_Id] ON [dbo].[Comments](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_FlagTypes_Id] ON [dbo].[FlagTypes](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_PendingFlags_Id] ON [dbo].[PendingFlags](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_PostFeedback_Id] ON [dbo].[PostFeedback](Id ASC);
CREATE NONCLUSTERED INDEX [IX_PostHistory_PostId_PostHistoryTypeId_UserId_Includes] ON [dbo].[PostHistory](PostId ASC, PostHistoryTypeId ASC, UserId ASC) INCLUDE (CreationDate);
CREATE UNIQUE CLUSTERED INDEX [UIX_PostHistory_Id] ON [dbo].[PostHistory](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_PostHistoryTypes_Id] ON [dbo].[PostHistoryTypes](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_PostLinks_Id] ON [dbo].[PostLinks](Id ASC);
CREATE NONCLUSTERED INDEX [IX_PostsWithDeleted_DeletionDate] ON [dbo].[PostsWithDeleted](DeletionDate ASC);
CREATE NONCLUSTERED INDEX [IX_PostsWithDeleted_OwnerUserId_CommunityOwnedDate_Includes] ON [dbo].[PostsWithDeleted](OwnerUserId ASC, CommunityOwnedDate ASC) INCLUDE (Id, ParentId);
CREATE NONCLUSTERED INDEX [IX_PostsWithDeleted_ParentId] ON [dbo].[PostsWithDeleted](ParentId ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_PostsWithDeleted_Id] ON [dbo].[PostsWithDeleted](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [IX_PostTags_PostId_TagId] ON [dbo].[PostTags](PostId ASC, TagId ASC);
CREATE UNIQUE NONCLUSTERED INDEX [IX_PostTags_TagId_PostId] ON [dbo].[PostTags](TagId ASC, PostId ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_PostTypes_Id] ON [dbo].[PostTypes](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_ReviewRejectionReasons_Id] ON [dbo].[ReviewRejectionReasons](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_ReviewTaskResults_Id] ON [dbo].[ReviewTaskResults](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_ReviewTaskResultTypes_Id] ON [dbo].[ReviewTaskResultTypes](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_ReviewTasks_Id] ON [dbo].[ReviewTasks](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_ReviewTaskStates_Id] ON [dbo].[ReviewTaskStates](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_ReviewTaskTypes_Id] ON [dbo].[ReviewTaskTypes](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_SuggestedEdits_Id] ON [dbo].[SuggestedEdits](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_SuggestedEditVotes_Id] ON [dbo].[SuggestedEditVotes](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [IX_Tags_Id] ON [dbo].[Tags](Id ASC);
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tags_TagName] ON [dbo].[Tags](TagName ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_TagSynonyms_Id] ON [dbo].[TagSynonyms](Id ASC);
/* CREATE NONCLUSTERED INDEX [IX_Users_EmailHash] ON [dbo].[Users](EmailHash ASC); */
CREATE UNIQUE CLUSTERED INDEX [UIX_Users_Id] ON [dbo].[Users](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_Votes_Id] ON [dbo].[Votes](Id ASC);
CREATE UNIQUE CLUSTERED INDEX [UIX_VoteTypes_Id] ON [dbo].[VoteTypes](Id ASC);

I don't think the full import script is very useful since we're pulling from the live SQL servers and not XML...but who knows! Here's a gist containing the stored procedure we use. If you have any questions, just throw a comment on here and I'll follow up.

Per May 26, 2024, a newly added index is:

CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId_PostId] 
   ON [Votes](VoteTypeId, PostId) 
   INCLUDE (CreationDate)