question: ‏The query as is is likely to perform sequential scans on the projects table which is now in the 400k+ range - we may need to add an index. Have you looked into that already?

Also, technically, this is just counting projects that have a publisher configured, but that doesn't mean they're actually using it.

We might want to count FileEvents from OIDC publishers instead:

select count(*) from file_events where additional->>'publisher_url' is not null;

and if we want to count # of projects, we can do the necessary joins from that.

@miketheman The compute_2fa_metrics task appears to be doing the same type of scan across projects, are you suggesting adding an index to the intermediary table between Project and OIDCPublisher or something else? (I haven't looked into anything yet)

@di I was wondering about whether projects that have used OIDC publishers versus configuring them would be nice to track as well. I suspect there will be some dwell time between configuring and actually using for some projects.

Per @di's suggestion I've distinguished between projects that have configured OIDC and have successfully published a release with OIDC in b9d6c0c. Hopefully the join-chain is what you were expecting?

@miketheman Do we need to change either of these queries to optimize them further? My SQLAlchemy-fu is a bit lacking so any suggestions would be helpful 🙏

Do we need to change either of these queries to optimize them further?

I'm gonna say "not yet" for now - let's let the task run and see how it does.