I have a large postgres table with a list of numbers that serve as a primary key. I have a second column that is an array of booleans. X% (from 0 to 100 in incriments of 5) of the rows need to append to the boolean array with a value of True, randomly, and the rest must append false.
Currently I have it updating with the queries below, but it is slow and because I need to run it ~10k times it is too slow. I have tried adding an index, and that did not appear to help.
- Am I selecting the rows with random incorrectly? Is there a faster way? This is a test so it needs to be as close to true randomness as possible, which is why I do not use TABLESAMPLE
- Each time I run this query, it does a join. Is there any way I can get it to not do a join? Maybe randomly update 5% of the rows with True, and the rest with false, or is this not possible with postgres?
Table schema:
name: ases
Column | Type | Collation | Nullable | Default
----------+-----------+-----------+----------+---------
asn | bigint | | |
as_types | boolean[] | | |
My query (The %s will be the number to update with True, calculated based on X%):
WITH adopting_ases AS (
SELECT asn, TRUE AS val FROM ases a WHERE a.asn != 5143
ORDER BY RANDOM() LIMIT %s)
UPDATE ases
SET as_types = array_append(as_types, e.val)
FROM adopting_ases b
WHERE b.asn = ases.asn;
Sorry if this is a dumb question, I've just been stuck on this for a while and cannot seem to figure out a way to do this query without joining a table onto itself, which seems incorrect. Thanks for all your help.
Aucun commentaire:
Enregistrer un commentaire