After upgrading to 2.9.0, reading LTL is takes too much time

After upgrading from 2.8.4 to 2.9.0 , it takes too much time (about 40 secs) to read local timeline using API timelines/public.
This is occurred only LTL not HTL and FTL.

I investigate it, and find that SQL as follows executed very slowly when read local timeline using API timelines/public.

SELECT "statuses"."id", "statuses"."updated_at" FROM "statuses" LEFT OUTER JOIN "accounts" ON "accounts"."id" = "statuses"."account_id" WHERE ("statuses"."local" = TRUE OR "statuses"."uri" IS NULL) AND "statuses"."visibility" = 0 AND (statuses.reblog_of_id IS NULL) AND (statuses.reply = FALSE OR statuses.in_reply_to_account_id = statuses.account_id) AND "statuses"."account_id" NOT IN (1092, 14899, 41089, 21990, 140, 26478, 1092, 10360, 241, 687, 97, 21) AND "accounts"."silenced_at" IS NULL ORDER BY "statuses"."id" DESC LIMIT 20;

The result of EXPLAIN ANALYZE of that SQL is as follows.

psql=> EXPLAIN ANALYZE SELECT “statuses”.“id”, “statuses”.“updated_at” FROM “statuses” LEFT OUTER JOIN “accounts” ON “accounts”.“id” = “statuses”.“account_id” WHERE (“statuses”.“local” = TRUE OR “statuses”.“uri” IS NULL) AND “statuses”.“visibility” = 0 AND (statuses.reblog_of_id IS NULL) AND (statuses.reply = FALSE OR statuses.in_reply_to_account_id = statuses.account_id) AND “statuses”.“account_id” NOT IN (1092, 14899, 41089, 21990, 140, 26478, 1092, 10360, 241, 687, 97, 21) AND “accounts”.“silenced_at” IS NULL ORDER BY “statuses”.“id” DESC LIMIT 20;
QUERY PLAN



Limit (cost=717014.12…717014.17 rows=20 width=16) (actual time=54049.051…54049.067 rows=20 loops=1)
-> Sort (cost=717014.12…717014.96 rows=337 width=16) (actual time=54049.049…54049.055 rows=20 loops=1)
Sort Key: statuses.id DESC
Sort Method: top-N heapsort Memory: 25kB
-> Hash Left Join (cost=7829.55…717005.15 rows=337 width=16) (actual time=56.080…54029.920 rows=21161 loops=1)
Hash Cond: (statuses.account_id = accounts.id)
Filter: (accounts.silenced_at IS NULL)
Rows Removed by Filter: 116
-> Seq Scan on statuses (cost=0.00…708248.04 rows=67459 width=24) (actual time=2.494…53947.400 rows=21277 loops=1)
Filter: ((local OR (uri IS NULL)) AND (reblog_of_id IS NULL) AND ((NOT reply) OR (in_reply_to_account_id = account_id)) AND (visibility = 0) AND (account_id <> ALL (’{1092,148
99,41089,21990,140,26478,1092,10360,241,687,97,21}’::bigint[])))
Rows Removed by Filter: 9306455
-> Hash (cost=7374.80…7374.80 rows=36380 width=16) (actual time=53.310…53.310 rows=36437 loops=1)
Buckets: 65536 Batches: 1 Memory Usage: 1936kB
-> Seq Scan on accounts (cost=0.00…7374.80 rows=36380 width=16) (actual time=0.007…40.646 rows=36437 loops=1)
Planning time: 0.429 ms
Execution time: 54049.137 ms

What is the root cause?
Was QUERY PLAN changed?

2 Likes

After I dump database and import it to a new database,this issue comes to not occurred.
As a result,I think root cause is database.
Thanks a lot!

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.