Learn more about these different git repos.
Other Git URLs
We're having many sessions now and it leads to ineffective SQL there. I can get getNextTask to 6 minutes now. Adding index on sessions will get it down to 20ms.
Hmm, just found, that we already have it in schema sessions_active_and_recent, just my instance was not updated to that.
sessions_active_and_recent
Anyway, difference between this compound index and simple sessions(active) is still about 2 orders of magnitude. Does it make sense to introduce also this one?
sessions(active)
brew=> EXPLAIN ANALYZE SELECT host.id,name,arches,task_load,capacity FROM host JOIN sessions USING (user_id) JOIN host_config ON host.id = host_config.host_id WHERE enabled = TRUE AND ready = TRUE AND expired = FALSE AND master IS NULL AND update_time > NOW() - '5 minutes'::interval AND active IS TRUE; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.98..2115.56 rows=1 width=62) (actual time=0.220..4.312 rows=147 loops=1) -> Nested Loop (cost=0.70..2115.20 rows=1 width=45) (actual time=0.213..3.844 rows=147 loops=1) -> Index Scan using sessions_expired on sessions (cost=0.42..2106.88 rows=1 width=4) (actual time=0.031..3.036 rows=174 loops=1) Index Cond: (expired = false) Filter: ((NOT expired) AND (master IS NULL) AND (update_time > (now() - '00:05:00'::interval))) Rows Removed by Filter: 1194 -> Index Scan using host_user_id on host (cost=0.28..8.30 rows=1 width=49) (actual time=0.003..0.004 rows=1 loops=174) Index Cond: (user_id = sessions.user_id) Filter: ready Rows Removed by Filter: 0 -> Index Scan using host_config_host_id_active_key on host_config (cost=0.28..0.36 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=147) Index Cond: ((host_id = host.id) AND (active = true)) Filter: (enabled AND (active IS TRUE)) Planning time: 0.535 ms Execution time: 4.354 ms
vs
QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=60.78..87992.20 rows=1 width=62) (actual time=0.252..607.307 rows=148 loops=1) -> Nested Loop (cost=10.40..1131.84 rows=99 width=66) (actual time=0.045..1.819 rows=153 loops=1) -> Bitmap Heap Scan on host_config (cost=10.11..55.90 rows=179 width=21) (actual time=0.037..0.283 rows=156 loops=1) Filter: (enabled AND (active IS TRUE)) Heap Blocks: exact=30 -> Bitmap Index Scan on host_config_by_active_and_enabled (cost=0.00..10.07 rows=179 width=0) (actual time=0.027..0.027 rows=156 loops=1) Index Cond: ((active = true) AND (enabled = true)) -> Index Scan using host_pkey on host (cost=0.28..6.00 rows=1 width=49) (actual time=0.006..0.008 rows=1 loops=156) Index Cond: (id = host_config.host_id) Filter: ready Rows Removed by Filter: 0 -> Bitmap Heap Scan on sessions (cost=50.38..877.37 rows=1 width=4) (actual time=0.929..3.944 rows=1 loops=153) Recheck Cond: (user_id = host.user_id) Filter: ((NOT expired) AND (master IS NULL) AND (update_time > (now() - '00:05:00'::interval))) Rows Removed by Filter: 2038 Heap Blocks: exact=292094 -> Bitmap Index Scan on sessions_user_id_key (cost=0.00..50.38 rows=1615 width=0) (actual time=0.469..0.469 rows=2224 loops=153) Index Cond: (user_id = host.user_id) Planning time: 0.543 ms Execution time: 607.401 ms (20 rows)
Metadata Update from @tkopecek: - Issue tagged with: discussion
What additional index do you propose? You seem to have added one, but it doesn't look like you've said what it is. We currently have:
Index just on 'expired' helped a lot compared to composed expired/master/update_time. It is used in first ANALYZE, second one is with that composed one. (In first case both existed and simple one was preferred, in second case only sessions_active_and_recent existed.
P.S. maybe it could be negative index like sessions(NOT expired) to help a bit more, but not sure if it makes any difference in this simple boolean index.
Suggested changes in #1479
Commit 1caa24a fixes this issue
Commit 03bf555 fixes this issue
Login to comment on this ticket.