vendredi 5 août 2016

Optimization of selection of semi-random rows in Postgres

I currently have a query that randomly selects a job from a table of jobs:

select jobs.job_id from jobs where (jobs.type is null) and (jobs.project_id = 5) and (jobs.status = 'Available') offset floor(random() * (select count(*) from jobs where (jobs.type is null) and (jobs.project_id = 5) and (jobs.status = 'Available'))) limit 1

This has the desired functionality, but is too slow. I am using Postgres 9.2 so I can't use TABLESAMPLE, unfortunately.

On the plus side, I do not need it to be truly random, so I'm thinking I can optimize it by making it slightly less random.

Any ideas?




Aucun commentaire:

Enregistrer un commentaire