NYCPlanning / NYCPlanning/data-engineering
aggregation queries in KPDB are slow
Nobody has claimed this yet.
- Dominant language
- Python
- Stars
- 43
- Forks
- 3
- Avg merge
- 23h 3m
- Merged PRs (30d)
- 44
Description
in KPDB builds (example), the aggregation SQL queries take ~2 hours. all other stages combined take ~20 minutes
although the queries appear repetitive/duplicative, it doesn't seem possible to abstract out common intermediate tables since the geographic boundaries unique to each aggregation are used so early
would love to use postgres features to analyze the queries
EXPLAIN (ANALYZE, BUFFERS)to get a query's execution plan- extenstions: pg_stat_statements,
pg_stat_kcache - maintaining and rebuilding spatial indexes
also these queries could maybe be combined into a single templated query by using either jinja or python/sqlalchemy, but that wouldn't necessarily speed them up
Contributor guide
No contributing guide indexed for this repository
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the KPDB build linked in the issue and locate the aggregation SQL queries that account for the reported runtime. Use EXPLAIN (ANALYZE, BUFFERS), pg_stat_statements, pg_stat_kcache, and spatial-index checks to identify the bottleneck; done means a documented and measurable reduction in aggregation time.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- postgresql, python, sql, sqlalchemy
- Domain
- data-engineering, databases, performance
- Issue type
- Refactor
- Difficulty
- 5/5
- Estimated time
- Over a week
- Activity status
- Stale
- Clarity
- Needs clarification
- Newbie friendliness
- 30/100