New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Consider using materialized views for common table expressions #210

Open

ohaibbq opened this issue Apr 11, 2024 · 0 comments

Contributor

ohaibbq commented Apr 11, 2024

@totem3 @goccy We've been utilizing this PR in our fork for quite a while and it seems to have performed well.
https://github.com/Recidiviz/go-zetasqlite/pull/32/files

Our use case never involves large swaths of data, but rather complex views containing few rows.

When using CTE materialization, it cut down one the query time for this view down from ~90seconds -> 4 seconds
https://github.com/Recidiviz/pulse-data/blob/main/recidiviz/ingest/direct/regions/us_me/ingest_views/view_CURRENT_STATUS_incarceration_periods_v2.py#L333-L570

The view contains ~20 CTEs with some nested CTEs.

For use cases of the emulator that operate on large amounts of data, the materialization of each CTE may be slower than without.

I'm opening this issue to discuss cherry-picking the change into the upstream repo.

@totem3 If you are experiencing performance issues for complex queries similar to this, I'd suggest trying out this small patch.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment