12

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.

1 Answer 1

17

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)
0

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .