Good evening, I have a word game which uses PostgreSQL 13.2 and 80% of the code is written as stored functions in PL/PgSQL or SQL.
Recently I have purchased some traffic and the number of daily games increased from 100 to 700. In the PostgreSQL log I have noticed that the duration for 2 particular queries have increased, especially in the evenings: 2021-02-19 17:51:19.104 CET [68932] LOG: duration: 2356.723 ms execute <unnamed>: SELECT words_stat_longest($1::int) 2021-02-19 17:55:23.290 CET [68602] LOG: duration: 2326.507 ms execute <unnamed>: SELECT words_stat_longest($1::int) 2021-02-19 17:57:57.057 CET [68932] LOG: duration: 1257.773 ms execute <unnamed>: SELECT out_uid AS uid, out_fcm AS fcm, out_apns AS apns, out_adm AS adm, out_hms AS hms, out_social AS social, out_sid AS sid, out_body AS body FROM words_play_game($1::int, $2::int, $3::jsonb) 2021-02-19 18:02:51.359 CET [68603] LOG: duration: 2305.950 ms execute <unnamed>: SELECT words_stat_longest($1::int) 2021-02-19 18:08:26.130 CET [68932] LOG: duration: 2375.713 ms execute <unnamed>: SELECT words_stat_longest($1::int) One of the queries is actually a simple SELECT ... LIMIT 15 to find the 15 longest words played by a user (they are displayed at the player profile page). I have run the EXPLAIN ANALYZE here: https://explain.depesz.com/s/IcqN It is difficult for me to understand, what could be missing there, does anybody please have an idea? Thank you for any input Alex P.S. Here are the tables involved in the SELECT query, with the words_moves and words_geoip holding the most records: # \d words_scores Table "public.words_scores" Column | Type | Collation | Nullable | Default --------+---------+-----------+----------+--------- mid | bigint | | not null | gid | integer | | not null | uid | integer | | not null | word | text | | not null | score | integer | | not null | Indexes: "words_scores_gid_idx" btree (gid) "words_scores_length_mid_idx" btree (length(word) DESC, mid DESC) "words_scores_uid_idx" btree (uid) Check constraints: "words_scores_score_check" CHECK (score >= 0) "words_scores_word_check" CHECK (word ~ '^[А-Я]{2,}$'::text) Foreign-key constraints: "words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE "words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE # \d words_moves Table "public.words_moves" Column | Type | Collation | Nullable | Default ---------+--------------------------+-----------+----------+------------------------------------------ mid | bigint | | not null | nextval('words_moves_mid_seq'::regclass) action | text | | not null | gid | integer | | not null | uid | integer | | not null | played | timestamp with time zone | | not null | tiles | jsonb | | | score | integer | | | str | text | | | hand | text | | | letters | character(1)[] | | | values | integer[] | | | Indexes: "words_moves_pkey" PRIMARY KEY, btree (mid) "words_moves_gid_played_idx" btree (gid, played DESC) "words_moves_uid_action_played_idx" btree (uid, action, played) "words_moves_uid_idx" btree (uid) Check constraints: "words_moves_score_check" CHECK (score >= 0) Foreign-key constraints: "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE Referenced by: TABLE "words_puzzles" CONSTRAINT "words_puzzles_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE # \d words_geoip Table "public.words_geoip" Column | Type | Collation | Nullable | Default --------+------------------+-----------+----------+--------- block | inet | | not null | lat | double precision | | | lng | double precision | | | Indexes: "words_geoip_pkey" PRIMARY KEY, btree (block) # \d words_games Table "public.words_games" Column | Type | Collation | Nullable | Default ----------+--------------------------+-----------+----------+------------------------------------------ gid | integer | | not null | nextval('words_games_gid_seq'::regclass) created | timestamp with time zone | | not null | finished | timestamp with time zone | | | player1 | integer | | not null | player2 | integer | | | played1 | timestamp with time zone | | | played2 | timestamp with time zone | | | state1 | text | | | state2 | text | | | reason | text | | | hint1 | text | | | hint2 | text | | | score1 | integer | | not null | score2 | integer | | not null | chat1 | integer | | not null | chat2 | integer | | not null | hand1 | character(1)[] | | not null | hand2 | character(1)[] | | not null | pile | character(1)[] | | not null | letters | character(1)[] | | not null | values | integer[] | | not null | bid | integer | | not null | diff1 | integer | | | diff2 | integer | | | open1 | boolean | | not null | false open2 | boolean | | not null | false Indexes: "words_games_pkey" PRIMARY KEY, btree (gid) "words_games_player1_coalesce_idx" btree (player1, COALESCE(finished, 'infinity'::timestamp with time zone)) "words_games_player2_coalesce_idx" btree (player2, COALESCE(finished, 'infinity'::timestamp with time zone)) Check constraints: "words_games_chat1_check" CHECK (chat1 >= 0) "words_games_chat2_check" CHECK (chat2 >= 0) "words_games_check" CHECK (player1 <> player2) "words_games_score1_check" CHECK (score1 >= 0) "words_games_score2_check" CHECK (score2 >= 0) Foreign-key constraints: "words_games_bid_fkey" FOREIGN KEY (bid) REFERENCES words_boards(bid) ON DELETE CASCADE "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE Referenced by: TABLE "words_chat" CONSTRAINT "words_chat_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE TABLE "words_moves" CONSTRAINT "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE TABLE "words_scores" CONSTRAINT "words_scores_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON DELETE CASCADE # \d words_users Table "public.words_users" Column | Type | Collation | Nullable | Default -------------+--------------------------+-----------+----------+------------------------------------------ uid | integer | | not null | nextval('words_users_uid_seq'::regclass) created | timestamp with time zone | | not null | visited | timestamp with time zone | | not null | ip | inet | | not null | fcm | text | | | apns | text | | | adm | text | | | motto | text | | | vip_until | timestamp with time zone | | | grand_until | timestamp with time zone | | | elo | integer | | not null | medals | integer | | not null | coins | integer | | not null | avg_score | double precision | | | avg_time | interval | | | hms | text | | | removed | boolean | | not null | false muted | boolean | | not null | false Indexes: "words_users_pkey" PRIMARY KEY, btree (uid) Check constraints: "words_users_elo_check" CHECK (elo >= 0) "words_users_medals_check" CHECK (medals >= 0) Referenced by: TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY (player1) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY (player2) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_puzzles" CONSTRAINT "words_puzzles_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_reviews" CONSTRAINT "words_reviews_author_fkey" FOREIGN KEY (author) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_reviews" CONSTRAINT "words_reviews_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE TABLE "words_stats" CONSTRAINT "words_stats_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE # \d words_social Table "public.words_social" Column | Type | Collation | Nullable | Default --------+------------------+-----------+----------+--------- sid | text | | not null | social | integer | | not null | given | text | | not null | family | text | | | photo | text | | | lat | double precision | | | lng | double precision | | | stamp | integer | | not null | uid | integer | | not null | Indexes: "words_social_pkey" PRIMARY KEY, btree (sid, social) "words_social_uid_stamp_idx" btree (uid, stamp DESC) Check constraints: "words_social_given_check" CHECK (given ~ '\S'::text) "words_social_photo_check" CHECK (photo ~* '^https?://...'::text) "words_social_sid_check" CHECK (sid ~ '\S'::text) "words_social_social_check" CHECK (0 < social AND social <= 128) Foreign-key constraints: "words_social_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON DELETE CASCADE Referenced by: TABLE "words_payments" CONSTRAINT "words_payments_sid_fkey" FOREIGN KEY (sid, social) REFERENCES words_social(sid, social) ON DELETE CASCADE "words_geoip_block_idx" spgist (block)