Skip to content

Instantly share code, notes, and snippets.

@shtrih

shtrih/plan.sql

Created February 12, 2023 16:00
Show Gist options
  • Save shtrih/2049e3c74f1bcd5770f8c40777c95104 to your computer and use it in GitHub Desktop.
Save shtrih/2049e3c74f1bcd5770f8c40777c95104 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
plan.sql
QUERY PLAN
GroupAggregate (cost=16654.78..16654.84 rows=2 width=68) (actual time=627.317..627.339 rows=1 loops=1)
" Group Key: t.date_end, (CASE WHEN ((t.date_end < '2022-12-30'::date) AND ((t.status)::text = 'open'::text)) THEN 'expired'::character varying ELSE t.status END)"
-> Sort (cost=16654.78..16654.79 rows=2 width=44) (actual time=627.292..627.314 rows=1 loops=1)
" Sort Key: t.date_end, (CASE WHEN ((t.date_end < '2022-12-30'::date) AND ((t.status)::text = 'open'::text)) THEN 'expired'::character varying ELSE t.status END)"
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on t (cost=16654.74..16654.77 rows=2 width=44) (actual time=627.273..627.295 rows=1 loops=1)
-> Sort (cost=16654.74..16654.74 rows=2 width=44) (actual time=627.271..627.292 rows=1 loops=1)
" Sort Key: ttt.date_end, ttt.status"
Sort Method: quicksort Memory: 25kB
-> Append (cost=924.55..16654.73 rows=2 width=44) (actual time=627.254..627.275 rows=1 loops=1)
-> GroupAggregate (cost=924.55..924.57 rows=1 width=44) (actual time=577.740..577.755 rows=0 loops=1)
" Group Key: ttt.date_end, ttt.status"
-> Sort (cost=924.55..924.55 rows=1 width=36) (actual time=577.739..577.753 rows=0 loops=1)
" Sort Key: ttt.date_end, ttt.status"
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on ttt (cost=924.50..924.54 rows=1 width=36) (actual time=577.727..577.741 rows=0 loops=1)
Filter: ((((ttt.status)::text = 'open'::text) AND (ttt.date_end <= '2022-12-30'::date)) OR (((ttt.status)::text IS DISTINCT FROM 'open'::text) AND (ttt.updated_at >= '2022-12-30 00:00:00'::timestamp without time zone)))
Rows Removed by Filter: 109
-> Unique (cost=924.50..924.52 rows=1 width=60) (actual time=577.672..577.718 rows=109 loops=1)
InitPlan 1 (returns $0)
-> Nested Loop (cost=5.14..36.60 rows=1 width=0) (actual time=0.548..0.552 rows=1 loops=1)
Join Filter: ((cm.channelid)::text = (c.id)::text)
Rows Removed by Join Filter: 6
-> Nested Loop (cost=4.86..28.28 rows=1 width=53) (actual time=0.355..0.398 rows=7 loops=1)
-> Nested Loop (cost=0.56..16.61 rows=1 width=54) (actual time=0.209..0.211 rows=1 loops=1)
-> Index Only Scan using contractorcustomers_pkey on contractorcustomers cc (cost=0.28..8.30 rows=1 width=53) (actual time=0.116..0.117 rows=1 loops=1)
Index Cond: ((contractorid = '1bcuf84uitfj7qpoafjap1g9ja'::text) AND (customerid = 'xcmdp3zp1pgrij1kciptoschih'::text))
Heap Fetches: 1
-> Index Scan using idx_teammembers_user_id on teammembers tm (cost=0.28..8.30 rows=1 width=54) (actual time=0.089..0.089 rows=1 loops=1)
Index Cond: ((userid)::text = 'kdm1a9anijbhpjeeom9afras4a'::text)
Filter: ((teamid)::text = '1bcuf84uitfj7qpoafjap1g9ja'::text)
-> Bitmap Heap Scan on channelmembers cm (cost=4.30..11.66 rows=1 width=52) (actual time=0.136..0.174 rows=7 loops=1)
Recheck Cond: ((userid)::text = 'kdm1a9anijbhpjeeom9afras4a'::text)
Filter: ((schemeuser IS TRUE) OR (schemeadmin IS TRUE) OR (schemeguest IS TRUE))
Heap Blocks: exact=3
-> Bitmap Index Scan on idx_channelmembers_user_id (cost=0.00..4.30 rows=2 width=0) (actual time=0.093..0.093 rows=15 loops=1)
Index Cond: ((userid)::text = 'kdm1a9anijbhpjeeom9afras4a'::text)
-> Index Scan using idx_channels_team_id on channels c (cost=0.28..8.30 rows=1 width=54) (actual time=0.019..0.020 rows=1 loops=7)
Index Cond: ((teamid)::text = 'xcmdp3zp1pgrij1kciptoschih'::text)
-> Sort (cost=887.91..887.91 rows=1 width=60) (actual time=577.671..577.685 rows=109 loops=1)
" Sort Key: t_1.id, t_1.date_end, (CASE WHEN (last_value(COALESCE(tsh.status, tts.status)) OVER (?) IS NULL) THEN 'open'::character varying ELSE last_value(COALESCE(tsh.status, tts.status)) OVER (?) END), (last_value(tsh.updated_at) OVER (?))"
Sort Method: quicksort Memory: 33kB
-> WindowAgg (cost=887.87..887.90 rows=1 width=60) (actual time=577.495..577.622 rows=109 loops=1)
-> Sort (cost=887.87..887.88 rows=1 width=32) (actual time=577.448..577.465 rows=109 loops=1)
" Sort Key: t_1.id, tsh.updated_at"
Sort Method: quicksort Memory: 30kB
-> Nested Loop Left Join (cost=33.42..887.86 rows=1 width=32) (actual time=27.959..577.048 rows=109 loops=1)
Filter: (((tts.status)::text = 'closed'::text) OR (((tts.status)::text IS DISTINCT FROM 'closed'::text) AND (t_1.deleted_at IS NULL) AND (SubPlan 2)))
Rows Removed by Filter: 2
-> Nested Loop Left Join (cost=33.14..874.67 rows=1 width=34) (actual time=13.917..573.364 rows=111 loops=1)
Filter: (tta.deleted_at IS NULL)
Rows Removed by Filter: 1
-> Nested Loop Left Join (cost=32.85..869.68 rows=1 width=34) (actual time=13.850..570.038 rows=112 loops=1)
Join Filter: (t_1.id = tsh.typical_task_id)
Rows Removed by Join Filter: 448
-> Merge Left Join (cost=32.85..113.18 rows=1 width=20) (actual time=1.218..2.904 rows=112 loops=1)
Merge Cond: (t_1.id = a.typical_task_id)
Filter: (((a.user_id)::text = 'kdm1a9anijbhpjeeom9afras4a'::text) OR ((a.user_id IS NULL) AND $0))
Rows Removed by Filter: 15
-> Index Scan using typical_tasks_pkey on typical_tasks t_1 (cost=0.28..80.25 rows=119 width=20) (actual time=0.043..1.312 rows=119 loops=1)
" Index Cond: (id = ANY ('{110,284,354,420,421,422,423,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,995,996,1024,1028,1029,1033,1034,1038,1077,1078,1079,1080,1110}'::bigint[]))"
-> Sort (cost=32.58..32.60 rows=9 width=35) (actual time=0.614..0.631 rows=19 loops=1)
Sort Key: a.typical_task_id
Sort Method: quicksort Memory: 26kB
-> Index Only Scan using typical_task_assignees_pkey on typical_task_assignees a (cost=0.29..32.43 rows=9 width=35) (actual time=0.303..0.580 rows=19 loops=1)
Index Cond: (team_id = 'xcmdp3zp1pgrij1kciptoschih'::text)
Heap Fetches: 18
-> Seq Scan on task_statistics tsh (cost=0.00..756.43 rows=6 width=22) (actual time=2.487..5.051 rows=4 loops=112)
Filter: ((updated_at <= '2022-12-30 23:59:59'::timestamp without time zone) AND ((team_id)::text = 'xcmdp3zp1pgrij1kciptoschih'::text))
Rows Removed by Filter: 21440
-> Index Scan using typical_task_attributes_pkey on typical_task_attributes tta (cost=0.28..4.97 rows=1 width=16) (actual time=0.021..0.021 rows=0 loops=112)
Index Cond: ((typical_task_id = t_1.id) AND ((team_id)::text = 'xcmdp3zp1pgrij1kciptoschih'::text))
-> Index Scan using typical_task_statuses_pkey on typical_task_statuses tts (cost=0.28..4.84 rows=1 width=14) (actual time=0.013..0.013 rows=0 loops=111)
Index Cond: (((team_id)::text = 'xcmdp3zp1pgrij1kciptoschih'::text) AND (typical_task_id = t_1.id))
SubPlan 2
-> Index Scan using contractorcustomers_pkey on contractorcustomers cc_1 (cost=0.28..8.34 rows=1 width=0) (actual time=0.014..0.014 rows=1 loops=105)
Index Cond: (((contractorid)::text = '1bcuf84uitfj7qpoafjap1g9ja'::text) AND ((customerid)::text = 'xcmdp3zp1pgrij1kciptoschih'::text))
Filter: (((status)::text = 'active'::text) OR CASE WHEN (archiveat = 0) THEN false ELSE (t_1.date_end < date((to_timestamp(((archiveat / 1000))::double precision))::timestamp without time zone)) END OR CASE WHEN (deleteat = 0) THEN false ELSE (t_1.date_end < date((to_timestamp(((deleteat / 1000))::double precision))::timestamp without time zone)) END)
-> GroupAggregate (cost=15730.11..15730.13 rows=1 width=44) (actual time=49.511..49.516 rows=1 loops=1)
" Group Key: ttt_1.date_end, ttt_1.status"
-> Sort (cost=15730.11..15730.11 rows=1 width=36) (actual time=49.504..49.509 rows=1 loops=1)
" Sort Key: ttt_1.date_end, ttt_1.status"
Sort Method: quicksort Memory: 25kB
-> Subquery Scan on ttt_1 (cost=15730.06..15730.10 rows=1 width=36) (actual time=49.291..49.490 rows=1 loops=1)
Filter: ((((ttt_1.status)::text = 'open'::text) AND (ttt_1.date_end <= '2022-12-30'::date)) OR (((ttt_1.status)::text IS DISTINCT FROM 'open'::text) AND (ttt_1.updated_at >= '2022-12-30 00:00:00'::timestamp without time zone)))
Rows Removed by Filter: 678
-> Unique (cost=15730.06..15730.08 rows=1 width=60) (actual time=49.174..49.378 rows=679 loops=1)
-> Sort (cost=15730.06..15730.07 rows=1 width=60) (actual time=49.173..49.217 rows=679 loops=1)
" Sort Key: t_2.id, t_2.date_end, (CASE WHEN (last_value(COALESCE(tsh_1.status, t_2.status)) OVER (?) IS NULL) THEN 'open'::character varying ELSE last_value(COALESCE(tsh_1.status, t_2.status)) OVER (?) END), (last_value(tsh_1.updated_at) OVER (?))"
Sort Method: quicksort Memory: 78kB
-> WindowAgg (cost=15730.03..15730.05 rows=1 width=60) (actual time=48.218..48.973 rows=679 loops=1)
-> Sort (cost=15730.03..15730.03 rows=1 width=31) (actual time=48.195..48.249 rows=679 loops=1)
" Sort Key: t_2.id, tsh_1.updated_at"
Sort Method: quicksort Memory: 78kB
-> Hash Right Join (cost=14976.88..15730.02 rows=1 width=31) (actual time=45.121..47.909 rows=679 loops=1)
Hash Cond: (tsh_1.task_id = t_2.id)
Join Filter: ((((t_2.team_id)::text = (tsh_1.team_id)::text) AND (tsh_1.updated_at <= '2022-12-30 23:59:59'::timestamp without time zone)) OR (tsh_1.team_id IS NULL))
-> Seq Scan on task_statistics tsh_1 (cost=0.00..699.53 rows=14292 width=49) (actual time=0.013..4.683 rows=12989 loops=1)
Filter: ((updated_at <= '2022-12-30 23:59:59'::timestamp without time zone) OR (team_id IS NULL))
Rows Removed by Filter: 8455
-> Hash (cost=14976.87..14976.87 rows=1 width=44) (actual time=42.042..42.045 rows=679 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 55kB
-> Nested Loop (cost=0.29..14976.87 rows=1 width=44) (actual time=14.717..41.462 rows=679 loops=1)
-> Seq Scan on task_assignees a_1 (cost=0.00..3485.88 rows=858 width=8) (actual time=14.637..32.707 rows=775 loops=1)
Filter: ((user_id)::text = 'kdm1a9anijbhpjeeom9afras4a'::text)
Rows Removed by Filter: 123617
-> Index Scan using tasks_pkey on tasks t_2 (cost=0.29..13.39 rows=1 width=44) (actual time=0.010..0.010 rows=1 loops=775)
Index Cond: (id = a_1.task_id)
" Filter: (((bookkeeper_team_id)::text = '1bcuf84uitfj7qpoafjap1g9ja'::text) AND ((team_id IS NULL) OR (((team_id)::text = ANY ('{uxs1nw1iz3db7cbs7ngn3xucse,brcubyddniynz8ygckyh7d6ydo,akbfikw7bify8csnet9zjd69xr,cphymwua63gfu8a6esuw5aq84h,5sy5utpbdfgdue58tg1ohugnua,w156kepzpbrqmgiswia863pgdw,z9gam6xtepredekokz987jwpmr,xcmdp3zp1pgrij1kciptoschih}'::text[])) AND (((status)::text = 'closed'::text) OR (((status)::text IS DISTINCT FROM 'closed'::text) AND (SubPlan 3))))))"
Rows Removed by Filter: 0
SubPlan 3
-> Index Scan using contractorcustomers_pkey on contractorcustomers cc_2 (cost=0.28..8.34 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=527)
Index Cond: (((contractorid)::text = (t_2.bookkeeper_team_id)::text) AND ((customerid)::text = (t_2.team_id)::text))
Filter: (((status)::text = 'active'::text) OR CASE WHEN (archiveat = 0) THEN false ELSE (t_2.date_end < date((to_timestamp(((archiveat / 1000))::double precision))::timestamp without time zone)) END OR CASE WHEN (deleteat = 0) THEN false ELSE (t_2.date_end < date((to_timestamp(((deleteat / 1000))::double precision))::timestamp without time zone)) END)
Rows Removed by Filter: 0
Planning Time: 10.372 ms
Execution Time: 628.291 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment