Pouria Hadjibagheri xenatisch

Principal Software Engineer | Bioinformatician | Systems Biologist | Data Scientist

xenatisch / cache_repopulation-normal.sql

Created November 21, 2021 12:02

Normal and optimised versions of Postgres (Citus) queries and plans thereof

	SELECT
	'area-' \|\| release_date::TEXT \|\| '-' \|\| area_id::TEXT AS key,
	JSONB_AGG(
	JSONB_BUILD_OBJECT(
	'area_code', area_code,
	'area_type', area_type,
	'area_name', area_name,
	'date', to_char(date::DATE, 'YYYY-MM-DD'),
	'metric', metric,
	'value', value,

xenatisch / query-plan.txt

Created November 29, 2021 10:28

Open Data API: Complex query sample

	Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=594) (actual time=14.645..14.648 rows=40 loops=1)
	Task Count: 32
	Tuple data received from nodes: 2240 bytes
	Tasks Shown: One of 32
	-> Task
	Tuple data received from node: 53 bytes
	Node: host=private-w6.[REDACTED] port=5432 dbname=citus
	-> Nested Loop (cost=0.71..196.77 rows=1 width=94) (actual time=0.120..2.282 rows=1 loops=1)
	Buffers: shared hit=190
	-> Nested Loop (cost=0.43..196.31 rows=1 width=162) (actual time=0.090..2.251 rows=1 loops=1)

xenatisch / covariance_map.ipynb

Last active January 4, 2022 11:41

Covariance map of case rates at UTLA level in England

Sorry, something went wrong. Reload?

Sorry, we cannot display this file.

Sorry, this file is invalid so it cannot be displayed.

xenatisch / to_decimal.r

Created January 5, 2023 12:40

Convert non-decimal integer numbers as string to decimal integers in R