Это на самом деле тестовый форум - просто провожу так сказать нагрузочное тестирование.
qwwq, Django - это фреймворк на питоне и форуму писал не я. Мне просто надо как-то что-то оптимизировать.
Я там менять ничего не могу. Тем более принцип работы пагинации. Сначала выбирается общее количество, потом разбивается на количество страниц, потом уже оффсетом выбирается нужные посты для нужной страницы.
В любой момент некоторые посты могут быть удалены. И соответственно посты сдвинутся.
Ну это так себе проблема, лишь несколько тем большие.
Оригинальную таблицу пришлось урезать. Вот план из таблицы на 100 000 строк
p2., фреймворк выполняет следующие запросы (из дебага):
1.
2.
3.
SELECT COUNT('*') AS "__count"
FROM "djangobb_forum_post"
WHERE "djangobb_forum_post"."topic_id" = 1
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
SELECT "djangobb_forum_post"."id",
"djangobb_forum_post"."topic_id",
"djangobb_forum_post"."user_id",
"djangobb_forum_post"."created",
"djangobb_forum_post"."updated",
"djangobb_forum_post"."updated_by_id",
"djangobb_forum_post"."markup",
"djangobb_forum_post"."body",
"djangobb_forum_post"."body_html",
"djangobb_forum_post"."user_ip",
"djangobb_forum_topic"."id",
"djangobb_forum_topic"."forum_id",
"djangobb_forum_topic"."name",
"djangobb_forum_topic"."created",
"djangobb_forum_topic"."updated",
"djangobb_forum_topic"."user_id",
"djangobb_forum_topic"."views",
"djangobb_forum_topic"."sticky",
"djangobb_forum_topic"."closed",
"djangobb_forum_topic"."post_count",
"djangobb_forum_topic"."last_post_id",
"djangobb_forum_forum"."id",
"djangobb_forum_forum"."category_id",
"djangobb_forum_forum"."name",
"djangobb_forum_forum"."position",
"djangobb_forum_forum"."description",
"djangobb_forum_forum"."updated",
"djangobb_forum_forum"."post_count",
"djangobb_forum_forum"."topic_count",
"djangobb_forum_forum"."last_post_id",
"djangobb_forum_forum"."forum_logo",
"djangobb_forum_category"."id",
"djangobb_forum_category"."name",
"djangobb_forum_category"."position",
"common_user"."id",
"common_user"."password",
"common_user"."last_login",
"common_user"."is_superuser",
"common_user"."username",
"common_user"."first_name",
"common_user"."last_name",
"common_user"."email",
"common_user"."is_staff",
"common_user"."is_active",
"common_user"."date_joined",
"common_user"."avatar",
"common_user"."country",
"common_user"."city",
T6."id",
T6."password",
T6."last_login",
T6."is_superuser",
T6."username",
T6."first_name",
T6."last_name",
T6."email",
T6."is_staff",
T6."is_active",
T6."date_joined",
T6."avatar",
T6."country",
T6."city"
FROM "djangobb_forum_post"
INNER JOIN "djangobb_forum_topic" ON ("djangobb_forum_post"."topic_id" = "djangobb_forum_topic"."id")
INNER JOIN "djangobb_forum_forum" ON ("djangobb_forum_topic"."forum_id" = "djangobb_forum_forum"."id")
INNER JOIN "djangobb_forum_category" ON ("djangobb_forum_forum"."category_id" = "djangobb_forum_category"."id")
INNER JOIN "common_user" ON ("djangobb_forum_topic"."user_id" = "common_user"."id")
INNER JOIN "common_user" T6 ON ("djangobb_forum_post"."user_id" = T6."id")
WHERE "djangobb_forum_post"."topic_id" = 1
ORDER BY "djangobb_forum_post"."created" ASC LIMIT 10
OFFSET 100000
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
"Limit (cost=19002.22..19002.22 rows=1 width=3502) (actual time=338.397..338.397 rows=1 loops=1)"
" Output: djangobb_forum_post.id, djangobb_forum_post.topic_id, djangobb_forum_post.user_id, djangobb_forum_post.created, djangobb_forum_post.updated, djangobb_forum_post.updated_by_id, djangobb_forum_post.markup, djangobb_forum_post.body, djangobb_forum_p (...)"
" Buffers: shared hit=22848"
" -> Nested Loop (cost=0.29..19002.22 rows=100000 width=3502) (actual time=0.100..328.335 rows=100001 loops=1)"
" Output: djangobb_forum_post.id, djangobb_forum_post.topic_id, djangobb_forum_post.user_id, djangobb_forum_post.created, djangobb_forum_post.updated, djangobb_forum_post.updated_by_id, djangobb_forum_post.markup, djangobb_forum_post.body, djangobb_f (...)"
" Join Filter: (djangobb_forum_post.user_id = t6.id)"
" Buffers: shared hit=22848"
" -> Nested Loop (cost=0.29..17501.21 rows=100000 width=2334) (actual time=0.081..213.849 rows=100001 loops=1)"
" Output: djangobb_forum_post.id, djangobb_forum_post.topic_id, djangobb_forum_post.user_id, djangobb_forum_post.created, djangobb_forum_post.updated, djangobb_forum_post.updated_by_id, djangobb_forum_post.markup, djangobb_forum_post.body, djan (...)"
" Buffers: shared hit=22847"
" -> Index Scan using djangobb_forum_post_created_7f4b3140f52c01f_uniq on public.djangobb_forum_post (cost=0.29..16247.03 rows=100000 width=1053) (actual time=0.027..69.759 rows=100001 loops=1)"
" Output: djangobb_forum_post.id, djangobb_forum_post.topic_id, djangobb_forum_post.user_id, djangobb_forum_post.created, djangobb_forum_post.updated, djangobb_forum_post.updated_by_id, djangobb_forum_post.markup, djangobb_forum_post.body (...)"
" Filter: (djangobb_forum_post.topic_id = 1)"
" Buffers: shared hit=22843"
" -> Materialize (cost=0.00..4.19 rows=1 width=1281) (actual time=0.000..0.000 rows=1 loops=100001)"
" Output: djangobb_forum_topic.id, djangobb_forum_topic.forum_id, djangobb_forum_topic.name, djangobb_forum_topic.created, djangobb_forum_topic.updated, djangobb_forum_topic.user_id, djangobb_forum_topic.views, djangobb_forum_topic.sticky (...)"
" Buffers: shared hit=4"
" -> Nested Loop (cost=0.00..4.18 rows=1 width=1281) (actual time=0.043..0.048 rows=1 loops=1)"
" Output: djangobb_forum_topic.id, djangobb_forum_topic.forum_id, djangobb_forum_topic.name, djangobb_forum_topic.created, djangobb_forum_topic.updated, djangobb_forum_topic.user_id, djangobb_forum_topic.views, djangobb_forum_topic. (...)"
" Join Filter: (djangobb_forum_topic.user_id = common_user.id)"
" Buffers: shared hit=4"
" -> Nested Loop (cost=0.00..3.16 rows=1 width=113) (actual time=0.032..0.036 rows=1 loops=1)"
" Output: djangobb_forum_topic.id, djangobb_forum_topic.forum_id, djangobb_forum_topic.name, djangobb_forum_topic.created, djangobb_forum_topic.updated, djangobb_forum_topic.user_id, djangobb_forum_topic.views, djangobb_forum_ (...)"
" Join Filter: (djangobb_forum_forum.category_id = djangobb_forum_category.id)"
" Rows Removed by Join Filter: 2"
" Buffers: shared hit=3"
" -> Nested Loop (cost=0.00..2.09 rows=1 width=90) (actual time=0.022..0.024 rows=1 loops=1)"
" Output: djangobb_forum_topic.id, djangobb_forum_topic.forum_id, djangobb_forum_topic.name, djangobb_forum_topic.created, djangobb_forum_topic.updated, djangobb_forum_topic.user_id, djangobb_forum_topic.views, djangobb_ (...)"
" Join Filter: (djangobb_forum_topic.forum_id = djangobb_forum_forum.id)"
" Rows Removed by Join Filter: 2"
" Buffers: shared hit=2"
" -> Seq Scan on public.djangobb_forum_topic (cost=0.00..1.02 rows=1 width=47) (actual time=0.011..0.012 rows=1 loops=1)"
" Output: djangobb_forum_topic.id, djangobb_forum_topic.forum_id, djangobb_forum_topic.name, djangobb_forum_topic.created, djangobb_forum_topic.updated, djangobb_forum_topic.user_id, djangobb_forum_topic.views, dja (...)"
" Filter: (djangobb_forum_topic.id = 1)"
" Rows Removed by Filter: 1"
" Buffers: shared hit=1"
" -> Seq Scan on public.djangobb_forum_forum (cost=0.00..1.03 rows=3 width=43) (actual time=0.003..0.004 rows=3 loops=1)"
" Output: djangobb_forum_forum.id, djangobb_forum_forum.category_id, djangobb_forum_forum.name, djangobb_forum_forum."position", djangobb_forum_forum.description, djangobb_forum_forum.updated, djangobb_forum_forum. (...)"
" Buffers: shared hit=1"
" -> Seq Scan on public.djangobb_forum_category (cost=0.00..1.03 rows=3 width=23) (actual time=0.002..0.003 rows=3 loops=1)"
" Output: djangobb_forum_category.id, djangobb_forum_category.name, djangobb_forum_category."position""
" Buffers: shared hit=1"
" -> Seq Scan on public.common_user (cost=0.00..1.01 rows=1 width=1168) (actual time=0.003..0.004 rows=1 loops=1)"
" Output: common_user.id, common_user.password, common_user.last_login, common_user.is_superuser, common_user.username, common_user.first_name, common_user.last_name, common_user.email, common_user.is_staff, common_user.is_act (...)"
" Buffers: shared hit=1"
" -> Materialize (cost=0.00..1.01 rows=1 width=1168) (actual time=0.000..0.000 rows=1 loops=100001)"
" Output: t6.id, t6.password, t6.last_login, t6.is_superuser, t6.username, t6.first_name, t6.last_name, t6.email, t6.is_staff, t6.is_active, t6.date_joined, t6.avatar, t6.country, t6.city"
" Buffers: shared hit=1"
" -> Seq Scan on public.common_user t6 (cost=0.00..1.01 rows=1 width=1168) (actual time=0.003..0.004 rows=1 loops=1)"
" Output: t6.id, t6.password, t6.last_login, t6.is_superuser, t6.username, t6.first_name, t6.last_name, t6.email, t6.is_staff, t6.is_active, t6.date_joined, t6.avatar, t6.country, t6.city"
" Buffers: shared hit=1"
"Total runtime: 338.690 ms"
Старую таблицу я скопировать отдельно, но план такой же каким я его видел и раньше:
1.
select * from djangobb_forum_post_backup OFFSET 1000000 LIMIT 10
1.
2.
3.
4.
5.
6.
7.
"Limit (cost=142359.49..142360.91 rows=10 width=1064) (actual time=11447.579..11447.584 rows=10 loops=1)"
" Output: id, topic_id, user_id, created, updated, updated_by_id, markup, body, body_html, user_ip"
" Buffers: shared read=132315"
" -> Seq Scan on public.djangobb_forum_post_backup (cost=0.00..187026.63 rows=1313763 width=1064) (actual time=4.212..11322.402 rows=1000010 loops=1)"
" Output: id, topic_id, user_id, created, updated, updated_by_id, markup, body, body_html, user_ip"
" Buffers: shared read=132315"
"Total runtime: 11447.626 ms"
|
