The MEGATOPIC: public good, or public menace?

Here’s a data explorer query that I wrote to attempt an answer to your question. You can try it with different topics and offsets.

-- [params]
-- int :topic_id = 107216
-- int :offset = 10000

SELECT "posts"."id" FROM "posts" 
WHERE ("posts"."deleted_at" IS NULL) 
AND "posts"."topic_id" = :topic_id
AND "posts"."post_type" IN (1,2,3) ORDER BY "posts"."sort_order" ASC LIMIT 20 
OFFSET :offset

Here’s a normal sized topic and a stupid-big topic:

Example time: 3.4ms
  ->  Index Scan using index_posts_on_topic_id_and_sort_order on posts  (cost=0.43..1925.22 rows=477 width=8)

example time: 353.9ms,  739.6 ms (time varies depending on database caching)
  ->  Index Scan using index_posts_on_topic_id_and_sort_order on posts  (cost=0.43..605155.88 rows=161255 width=8)

I think that I have seen times longer than 750ms.

Here are the Median and 99th percentile times. Median time is surprisingly good, but the nature of a median is that you can’t tell whether at the 60% percentile it’s much much worse than the median case.

Here’s another server (it has a stupid number of categories, which gives it a performance hit too, so it’s not apples to apples without megatopics), but you can see the median performance is half and the worst case is much better too:

11 Likes