How to search in Stack Exchange Data Explorer (SEDE)

Question

In Data Explorer, I would like to SELECT the same posts that would show up if I'd run a search on Stack Overflow.

For example, when I run this search on Stack Overflow, it currently shows 604,031 results. These posts I would like to analyze in Data Explorer.

What condition do I have to put in?

(Actually I was trying to find out whether "postgresql explain" is more on topic on Stack Overflow or Database Administrators, by comparing their accept rates.)

Answer 1

You will break the Data Explorer with that query:

select  id as [Post Link]
from posts
where body like '%if%'
or body like '%loop%'

This will end in an error:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Because there are no full text search indexes present in the SQL Server instance SEDE is running on. The query has to do a full scan of all rows in the Posts table. Beyond that, SEDE will never return more that 50.000 rows. SEDE is also only refreshed weekly.

If you limit the scope to a specific tag, for example progresql and gather some stats you can get some results:

select sum(case
       when acceptedanswerid is not null
       then 1
       else 0 end) as [accepted]
       , count(*) as [all]
from posts  p
inner join posttags pt on pt.postid = p.id
inner join tags t on t.id = pt.tagid
where t.tagname = 'postgresql'
and p.body like '%explain%'

By switching the site you can run this on other databases and compare their outcome.