有人看到了吗,为什么PostgreSQL 10.6中的以下select查询会花费更长的时间,尽管它有一个索引。
我正在尝试查找在过去一个月内至少执行过一次移动的所有用户(以及
display them in a PHP script
):
SELECT
u.uid,
u.elo,
TO_CHAR(avg_time, 'HH24:MI') AS avg_time,
ROUND(avg_score::numeric, 1) AS avg_score,
s.given,
s.photo
FROM words_users u
LEFT JOIN LATERAL (SELECT * FROM words_social s WHERE s.uid = u.uid ORDER BY s.stamp DESC LIMIT 1) AS s ON TRUE
WHERE EXISTS (SELECT 1
FROM words_moves m
WHERE u.uid = m.uid
AND m.action = 'play'
AND m.played > CURRENT_TIMESTAMP - INTERVAL '1 month')
ORDER BY u.elo DESC;
结果如下
EXPLAIN ANALYZE output
:
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=20075.91..20078.03 rows=848 width=160) (actual time=433.461..433.477 rows=219 loops=1)
Sort Key: u.elo DESC
Sort Method: quicksort Memory: 58kB
-> Nested Loop Left Join (cost=0.71..20034.67 rows=848 width=160) (actual time=0.065..433.315 rows=219 loops=1)
-> Nested Loop Semi Join (cost=0.43..12970.83 rows=848 width=32) (actual time=0.049..431.592 rows=219 loops=1)
-> Seq Scan on words_users u (cost=0.00..391.02 rows=10702 width=32) (actual time=0.005..2.351 rows=10702 loops=1)
-> Index Scan using words_moves_uid_idx on words_moves m (cost=0.43..80.04 rows=115 width=4) (actual time=0.040..0.040 rows=0 loops=10702)
Index Cond: (uid = u.uid)
Filter: ((action = 'play'::text) AND (played > (CURRENT_TIMESTAMP - '1 mon'::interval)))
Rows Removed by Filter: 35
-> Limit (cost=0.29..8.30 rows=1 width=180) (actual time=0.004..0.005 rows=1 loops=219)
-> Index Scan using words_social_uid_stamp_idx on words_social s (cost=0.29..8.30 rows=1 width=180) (actual time=0.004..0.004 rows=1 loops=219)
Index Cond: (uid = u.uid)
Planning time: 0.289 ms
Execution time: 433.526 ms
(15 rows)
为什么索引扫描需要更长时间?
下面是使用的表和索引-
词动:
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)
"words_moves_gid_played_idx" btree (gid, played DESC)
"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_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
用户:
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 |
avg_score | double precision | | |
avg_time | interval | | |
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
社会:
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_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
以下是我创建的索引(除了pk):
CREATE INDEX ON words_moves (gid, played);
CREATE INDEX ON words_moves (uid);
更新:
根据劳伦茨的建议,增加另一个指数有助于-
CREATE INDEX ON words_moves (uid, action, played);
这里是新的
EXPLAIN ANALYZE output
:
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=14203.82..14205.94 rows=848 width=160) (actual time=19.766..19.780 rows=219 loops=1)
Sort Key: u.elo DESC
Sort Method: quicksort Memory: 58kB
-> Nested Loop Left Join (cost=0.72..14162.57 rows=848 width=160) (actual time=0.105..19.695 rows=219 loops=1)
-> Nested Loop Semi Join (cost=0.43..7098.73 rows=848 width=32) (actual time=0.082..18.644 rows=219 loops=1)
-> Seq Scan on words_users u (cost=0.00..391.02 rows=10702 width=32) (actual time=0.011..1.920 rows=10703 loops=1)
-> Index Only Scan using words_moves_uid_action_played_idx on words_moves m (cost=0.43..20.82 rows=114 width=4) (actual time=0.001..0.001 rows=0 loops=10703)
Index Cond: ((uid = u.uid) AND (action = 'play'::text) AND (played > (CURRENT_TIMESTAMP - '1 mon'::interval)))
Heap Fetches: 219
-> Limit (cost=0.29..8.30 rows=1 width=180) (actual time=0.003..0.003 rows=1 loops=219)
-> Index Scan using words_social_uid_stamp_idx on words_social s (cost=0.29..8.30 rows=1 width=180) (actual time=0.003..0.003 rows=1 loops=219)
Index Cond: (uid = u.uid)
Planning time: 0.456 ms
Execution time: 19.813 ms
(14 rows)
