Information about database indexes in the Data Explorer

Question

Some days ago I discovered the Stack Exchange Data Explorer (SEDE) which provides already lots of interesting queries out of the box (thanks to the contributors).

After a while I noticed that some of the queries are running very long. In fact, they ran so long that they were cancelled by the system, which showed the following message:

Something unexpected went wrong while running your query. Don't worry, blame is already being assigned.

One example is the Top Users by Country list. Well, I thought this isn't really a big issue, because I can (try to) enhance the query myself. So I opened the query composer, saw the SQL text field on the left and the database schema on the right. A quick search returned some additional descriptions of the tables the columns.

But what I missed was a description of indexes and foreign keys, because they have a big influence on how I build a query and they are quite essential for query optimization.

So my questions: can I find this information somewhere? Or even better: could this be added to the SEDE? I'd appreciate it as well, if I could evaluate an execution plan without running the query. Would that be possible?

p.s.: as per comment here is an execution plan from the SEDE for the simple statement below:

SELECT ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [#], 
       Id AS [User Link], 
       Reputation
  FROM Users
 WHERE id = 1 
 ORDER BY Reputation DESC;

This information is very helpful for query optimization - in particular for more complex and long running statements.

Answer 1

There are no foreign keys defined in the imported databases. I have an open feature request for that here.

As the rdbms SEDE runs its query on is SqlSever and we have select permission on the schema tables/views we can query for the available indexes:

select object_name(i.object_id) as [table]
     , i.name
     , i.type_desc
     , i.is_unique
from sys.indexes i

or use the INFORMATION_SCHEMA views:

select *
from information_schema.table_CONSTRAINTS 

You could get SEDE to only report the execution plan by using the SET SHOWPLAN_TEXT statement

SET SHOWPLAN_TEXT ON
go
select * from posts
go
 

Do notice that this gives you a multiple resultset, click on the number to cycle through them.