Re: Adding LEFT JOIN to a query has increased execution time 10 times - Mailing list pgsql-general
| From | Alexander Farber |
|---|---|
| Subject | Re: Adding LEFT JOIN to a query has increased execution time 10 times |
| Date | |
| Msg-id | CAADeyWjgPwKXw9_ZAMXxUxmV=R=ZR57AaC2Ebf_eewWmnOBoFw@mail.gmail.com Whole thread Raw |
| In response to | Adding LEFT JOIN to a query has increased execution time 10 times (Alexander Farber <alexander.farber@gmail.com>) |
| List | pgsql-general |
I have 6 times "Seq Scan", I have to get rid of that by adding indices, correct?
So I have added already 2 and the time has improved:
So I have added already 2 and the time has improved:
CREATE INDEX words_moves_gid_index ON words_moves(gid);
CREATE INDEX words_moves_uid_index ON words_social(uid);
But unfortunately I still don't understand, which INDEX to add for words_geoip, because there is already one (the PK):
# \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)
For reference, here are all my tables:
# \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 | friendly | boolean | | |
Indexes: "words_games_pkey" PRIMARY KEY, btree (gid) "words_games_reason_index" btree (reason) "words_games_state1_index" btree (state1) "words_games_state2_index" btree (state2)
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 | | | banned_until | timestamp with time zone | | | banned_reason | text | | | elo | integer | | not null | medals | integer | | not null | coins | integer | | not null |
Indexes: "words_users_pkey" PRIMARY KEY, btree (uid)
Check constraints: "words_users_banned_reason_check" CHECK (length(banned_reason) > 0) "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_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)
Check constraints: "words_social_given_check" CHECK (given ~ '\S'::text) "words_social_photo_check" CHECK (photo ~* '^https?://...'::text) "words_social_social_check" CHECK (0 < social AND social <= 64)
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
# \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 | | | letters | text | | | hand | text | | | puzzle | boolean | | not null | false
Indexes: "words_moves_pkey" PRIMARY KEY, btree (mid)
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_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADEThank you for any hints
Alex
pgsql-general by date: