Skip to content
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.

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

Extract data_item.data ->> 'date' to release_date column #131

Open
jpmckinney opened this issue Oct 4, 2024 · 1 comment
Open

Extract data_item.data ->> 'date' to release_date column #131

jpmckinney opened this issue Oct 4, 2024 · 1 comment

Comments

@jpmckinney
Copy link
Member

jpmckinney commented Oct 4, 2024

Similar to the index added in Kingfisher Process.

Affects pelican-backend/workers/extract/dataset_filter.py and pelican-frontend/backend/api/views.py

Older note in 001_base.sql:

-- data_item is the largest and most frequently queried table, so rarely used indexes are avoided. The queries in
-- workers/extract/dataset_filter.py and pelican-frontend/backend/api/views.py are rarely run, so we don't add the
-- indexes for data->>'date', data->'buyer'->>'name' and data->'tender'->'procuringEntity'->>'name' (text_pattern_ops).

The query in views is called for each job in the registry, so it might now make sense to add the index. Per the comment, though, we can check whether it slows down data processing to populate the index.

@jpmckinney jpmckinney changed the title Add index on data_item.data ->> 'date' Add index on data_item.data ->> 'date' Oct 4, 2024
@jpmckinney
Copy link
Member Author

Alternatively, add the release date as a separate column.

@jpmckinney jpmckinney changed the title Add index on data_item.data ->> 'date' Extract data_item.data ->> 'date' to release_date column Oct 8, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant