vendredi 17 avril 2020

Randomly updating PostgreSQL table

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.

  1. 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
  2. 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