rubenf
rubenf5d ago

Can you connect to your postgresql

Can you connect to your postgresql database with psql and show me the result of: \d v2_job; Next, can you run:
EXPLAIN ANALYZE SELECT
schedule.*, t.jobs FROM schedule,
LATERAL(SELECT ARRAY(
SELECT json_build_object('id', id, 'success', status = 'success', 'duration_ms', duration_ms)
FROM v2_job_completed c JOIN v2_job j USING (id)
WHERE trigger_kind = 'schedule'
AND trigger = schedule.path
AND c.workspace_id = 'admins'
AND j.workspace_id = 'admins'
AND parent_job IS NULL AND runnable_path = schedule.script_path
AND status <> 'skipped'
ORDER BY created_at DESC
LIMIT 20
) AS jobs) t
WHERE workspace_id = 'admins'
ORDER BY edited_at DESC;
EXPLAIN ANALYZE SELECT
schedule.*, t.jobs FROM schedule,
LATERAL(SELECT ARRAY(
SELECT json_build_object('id', id, 'success', status = 'success', 'duration_ms', duration_ms)
FROM v2_job_completed c JOIN v2_job j USING (id)
WHERE trigger_kind = 'schedule'
AND trigger = schedule.path
AND c.workspace_id = 'admins'
AND j.workspace_id = 'admins'
AND parent_job IS NULL AND runnable_path = schedule.script_path
AND status <> 'skipped'
ORDER BY created_at DESC
LIMIT 20
) AS jobs) t
WHERE workspace_id = 'admins'
ORDER BY edited_at DESC;
Where you have replaced 3 times 'admins' with the name of your workspace in the query above.
13 Replies
Alper
Alper5d ago
No description
rubenf
rubenfOP5d ago
@Alper I need to know the indices
Alper
Alper5d ago
No description
Alper
Alper5d ago
the explain query is now running for 5 minutes
Alper
Alper5d ago
explain result after 5:30
No description
rubenf
rubenfOP5d ago
you're missing an index for some reasons what version are you on?
Alper
Alper5d ago
No description
rubenf
rubenfOP5d ago
you're missing this index somehow:
"root_job_index_by_path_2" btree (workspace_id, runnable_path, created_at DESC) WHERE parent_job IS NULL
"root_job_index_by_path_2" btree (workspace_id, runnable_path, created_at DESC) WHERE parent_job IS NULL
Alper
Alper5d ago
should i add it manually?
rubenf
rubenfOP5d ago
no need to find out why it wasn't created @Alper do you have the logs of your servers/workers?
Alper
Alper5d ago
i have
rubenf
rubenfOP5d ago
can you run:
select * from windmill_migrations ;
select * from windmill_migrations ;
Alper
Alper5d ago
No description

Did you find this page helpful?