17

In the Stack Exchange Data Explorer only the most logical/beneficial yet minimal set of indexes is created for the tables found in the schema.

One of those indexes is on the Users table:

CREATE NONCLUSTERED INDEX [IX_Users_EmailHash] ON [dbo].[Users](EmailHash ASC)

That is a not so useful index because the EmailHash field is always null. It was emptied after it was found some external service didn't had their stuff sorted.

On the Votes table we only have:

CREATE UNIQUE CLUSTERED INDEX [UIX_Votes_Id] ON [dbo].[Votes](Id ASC)

On Stack Overflow that table has 239,931,862 rows. We vote too much. Stop voting!

There are enough queries where you want to check for patterns in posts that received certain votes. Selecting those rows out of that table never gets optimized due to the lack of a proper index.

I suggest we exchange the no longer useful index on Users.EmailHash with an index on Votes.VoteTypeId. This exchange is in the spirit of being on a budget where we rather improve on existing things instead of just adding new bloat.

CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId] ON [dbo].[Votes](VoteTypeId ASC)

(adding INCLUDE (PostId, CreationDate) will lead to the index being used by other common scenario's as well. This would make the index similar to the index that already exists on PostHistory)

VoteTypeId is a tinyint and together with its integer key it shouldn't take up too much space. Based on this query I reckon the distribution of values and rows it covers is reasonable.

I know proper database tuning and execution plan optimization is done based on more evidence and study of typical query usage. SEDE is a bit hard to predict because you never know what horror queries users will throw at it tomorrow. My expectation is that this index will help a bit, maybe there is a size/performance trade-off with an even wider index.

Please consider adding an index on VoteTypeId on the Votes table.

7
  • 2
    That index might help on this query attempt as well: meta.stackexchange.com/a/400005
    – rene
    Commented May 18 at 19:47
  • SEDE is also tough because there is no self-contained running history… even if they have query store enabled (which I doubt), it would get blown away every weekend, and their own query and plan logging gets deleted as part of the refresh routine IIRC. I do wonder how much the index would help… yes you do a whole table scan but for queries against just up or just down votes, you’re scanning roughly half the table in either case? Commented May 19 at 13:13
  • @testing-ma-lady yeah, I'm looking now if an index with postid and/or creationdate is maybe a better alternative. I'm open to better suggestion and I appreciate an answer with your analysis.
    – rene
    Commented May 19 at 13:19
  • For the query you link to, a supporting index (maybe even a filtered index per vote type) might be much more feasible if it were SELECT PostId ... instead of SELECT * .... It's possible SQL Server optimizes that away, but I wouldn't rely on a guarantee, especially with a filtered index. The right choice(s), without covering every possible scenario, will probably depend on some analysis of the most frequent (and problematic) query patterns and where the best opportunities are. I see some going after 10, some going after (1,2,3,9), etc. Commented May 19 at 13:56
  • @testing-ma-lady I'm wondering if dba.se would take this question? Given SEDE and the schema, what index(es) should the Votes table have.
    – rene
    Commented May 19 at 20:26
  • 3
    "On Stack Overflow that table has 239,931,862 rows. We vote too much. Stop voting!" Stack Overflow having what averages out to about 3 votes per post (questions and answers combined, including deleted ones - based on currently generated post IDs) is not a problem. The problem is too much gets posted that should never have been, and not enough of the votes are downward. (I get that you were being facetious, but I still feel I should take the opportunity to say it.) Commented May 20 at 22:30
  • 4
    Moved this to [status-planned]. This change is likely being made with the next Data Explorer refresh.
    – Slate StaffMod
    Commented May 23 at 14:41

1 Answer 1

5

Per May 26, 2024 the newly added index is:

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

This can be verified here. The index on EmailHash on the users table is dropped as well.

For posterity I'll keep the several considered alternatives below


I have tested on the Meta Stack Exchange database more index alternatives to the one suggested in the question here:

There are 5 index options now:

-- option 1 (same as in the question)
CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId] 
   ON [#Votes](VoteTypeId ASC) 

-- option 2
CREATE NONCLUSTERED INDEX [IX_Votes_VoteTypeId_Include] 
   ON [#Votes](VoteTypeId ASC) 
   INCLUDE (PostId, CreationDate)

-- option 3
CREATE NONCLUSTERED INDEX [IX_Votes_PostId] ON [#Votes](PostId ASC)

-- option 4
CREATE NONCLUSTERED INDEX [IX_Votes_CreationDate] 
    ON [#Votes](CreationDate ASC)
    INCLUDE (votetypeid)

-- option 5
CREATE NONCLUSTERED INDEX [IX_Votes_PostId_VoteTypeId_Include] 
    ON [#Votes](PostId ASC, VoteTypeId ASC) 
    INCLUDE (CreationDate) 

I created 3 "typical" queries to evaluate each index:

select count(*) [bounties]
from #votes
where votetypeid = 8

select postid, score
from posts p
inner join #votes on postid = p.id
where votetypeid = 9

select count(*) [accepts in date range]
from #votes
where creationdate > '2020-01-01' -- and '2022-04-01'
and votetypeid = 1

Per index I have examined the Show Plan output to look for Index Seek operation as opposed to Scan operations.

In this testing I've found that option 2 gives the most bang for my buck. Given your mileage may vary and my inability to test this against the SO database I assume the SE Dba that takes this feature request on finds a better optimized index.

You must log in to answer this question.

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