dimanche 7 février 2021

faster order by rand() while don't have an id

I'm using postgresql 12.1 to perform that task.

I have a table called numbers with a relevant column called number_a, this table doesn't not have an auto incremented primary key.

I need to fetch random numbers from it, the problem is that this table is pretty huge, could be close to 1b of rows. so even if i select one row by random it takes a long time.

executing select number_1 from numbers order by random() limit 1 is really really slow

I googled and found some websites that explain that you can you the increment id, to to use that to find random keys, but i don't have it in this table and I can't modify it.

i googled some more and found some postgresql extensions, but i prefer not to install a 3rd party extension just for this kind of a solution.

so does anyhow know how to properly select by rand when i don't have an incremental key ?

I can't change the table.

thank you.




Aucun commentaire:

Enregistrer un commentaire