NotificationsYou must be signed in to change notification settings
Fork928
Star10.1k

Commit47fd190

authored

fix(coderd/database): improve perf ofGetTemplateInsightsByInterval (#12773)

1 parentba1eace commit47fd190Copy full SHA for 47fd190

File tree

2 files changed

+26

-18

lines changed

coderd/database
- queries
  - insights.sql
- queries.sql.go

2 files changed

+26

-18

lines changed

`‎coderd/database/queries.sql.go`

Lines changed: 13 additions & 9 deletions

Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

`‎coderd/database/queries/insights.sql`

Lines changed: 13 additions & 9 deletions

Original file line number	Diff line number	Diff line change
`@@ -355,7 +355,7 @@ WITH`
`355`	`355`	`CROSS JOIN`
`356`	`356`	`generate_series(`
`357`	`357`	`date_trunc('minute',was.session_started_at),`
`358`		`--- Subtract 1microsecond to avoid creating an extra series.`
	`358`	`+-- Subtract 1μs to avoid creating an extra series.`
`359`	`359`	`date_trunc('minute',was.session_ended_at-'1 microsecond'::interval),`
`360`	`360`	`'1 minute'::interval`
`361`	`361`	`)AS s(minute_bucket)`
`@@ -389,14 +389,17 @@ WITH`
`389`	`389`	`tsAS (`
`390`	`390`	`SELECT`
`391`	`391`	`d::timestamptzAS from_,`
`392`		`-CASE`
`393`		`-WHEN (d::timestamptz+ (@interval_days::int\|\|' day')::interval)<= @end_time::timestamptz`
`394`		`-THEN (d::timestamptz+ (@interval_days::int\|\|' day')::interval)`
`395`		`-ELSE @end_time::timestamptz`
`396`		`-ENDAS to_`
	`392`	`+LEAST(`
	`393`	`+(d::timestamptz+ (@interval_days::int\|\|' day')::interval)::timestamptz,`
	`394`	`+@end_time::timestamptz`
	`395`	`+)::timestamptzAS to_`
`397`	`396`	`FROM`
`398`		`--- Subtract 1 microsecond from end_time to avoid including the next interval in the results.`
`399`		`-generate_series(@start_time::timestamptz, (@end_time::timestamptz)-'1 microsecond'::interval, (@interval_days::int\|\|' day')::interval)AS d`
	`397`	`+generate_series(`
	`398`	`+@start_time::timestamptz,`
	`399`	`+-- Subtract 1 μs to avoid creating an extra series.`
	`400`	`+(@end_time::timestamptz)-'1 microsecond'::interval,`
	`401`	`+(@interval_days::int\|\|' day')::interval`
	`402`	`+)AS d`
`400`	`403`	`)`
`401`	`404`
`402`	`405`	`SELECT`
`@@ -410,6 +413,7 @@ LEFT JOIN`
`410`	`413`	`template_usage_statsAS tus`
`411`	`414`	`ON`
`412`	`415`	`tus.start_time>=ts.from_`
	`416`	`+ANDtus.start_time<ts.to_-- End time exclusion criteria optimization for index.`
`413`	`417`	`ANDtus.end_time<=ts.to_`
`414`	`418`	`AND CASE WHEN COALESCE(array_length(@template_ids::uuid[],1),0)>0 THENtus.template_id= ANY(@template_ids::uuid[]) ELSE TRUE END`
`415`	`419`	`GROUP BY`
`@@ -473,7 +477,7 @@ WITH`
`473`	`477`	`CROSS JOIN`
`474`	`478`	`generate_series(`
`475`	`479`	`date_trunc('minute',was.session_started_at),`
`476`		`--- Subtract 1microsecond to avoid creating an extra series.`
	`480`	`+-- Subtract 1μs to avoid creating an extra series.`
`477`	`481`	`date_trunc('minute',was.session_ended_at-'1 microsecond'::interval),`
`478`	`482`	`'1 minute'::interval`
`479`	`483`	`)AS s(minute_bucket)`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commit47fd190

File tree

2 files changed

2 files changed

`‎coderd/database/queries.sql.go`

`‎coderd/database/queries/insights.sql`

0 commit comments