Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oCl3E-0005J4-0z for pgsql-general@arkaria.postgresql.org; Sat, 16 Jul 2022 16:53:56 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oCl3C-0004wm-EM for pgsql-general@arkaria.postgresql.org; Sat, 16 Jul 2022 16:53:54 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oCl3C-0004wc-1U for pgsql-general@lists.postgresql.org; Sat, 16 Jul 2022 16:53:54 +0000 Received: from mail-qv1-xf33.google.com ([2607:f8b0:4864:20::f33]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oCl39-0000IY-Iv for pgsql-general@lists.postgresql.org; Sat, 16 Jul 2022 16:53:53 +0000 Received: by mail-qv1-xf33.google.com with SMTP id l2so5847277qvt.2 for ; Sat, 16 Jul 2022 09:53:51 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=+wkMIidhDOV/jIJ/6qbVwuu38rnVBA3z5ghBnsCHTew=; b=iQCeIZ+PZyzETd2nDFyJOqDdvWWBM/+nMt8oA4HoVixX8NaEyq5yH1uoWjlaQ4s/SK aM271t4k0y8/h+uYD4isqiy0byT76eH3iiEiyolGq7U4nlyrp+SRAOBdDuDTeOcqoONk 2YfxhoRuIUccTJnp/ek/Pe78YJksfpeF45yUwDFoNB+A36AaRbqA2ziF5w0LEYLG6g/V A2yjlE6CTwaZLY3dpaGBdKMA+QgL8t/XA6UWTeiZcZQkXBb1X7tT7ijhzza0wpxyqcJD oS+CW71si8/qn6Lo3Lcw5yfV3fy807unqLMy5RJ0cgNjUbtnvJcnz9tbvsJ2gzYE7d0h kfXA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=+wkMIidhDOV/jIJ/6qbVwuu38rnVBA3z5ghBnsCHTew=; b=3/pAOKp830F/iSRjaBNpmRppa83bw8UosaH3NOIzKEE0USPRVxMKVrw4LtIeY2DEcn Ej2VbmlRvOfWGawdAcT0jNhdbm6H/h6krBTuxl/B/yOwtCdOwlzQHbzAU1Bb7YwzeAKj /8bDoCgoOybUiMldNbxVnCei0pNDeeipMkfj6qsXG+RUfiweNYWHTp7T2I0sVN9rD7o4 oAXK7NYCqulAiB+LbPZVvR2Ua23BjBLyNClmUBoTUVzX5ZDBnlIOrf7ZdxJqlNPQ38Yh KBStbQ7lXRot+6+7kljQsSpylitS/n3WHZUIa8U+wGvKYiDzkIQxbrZp+6xQmfu8TQRu MYgg== X-Gm-Message-State: AJIora83BIpRTJM2S90f+9Z20GrC24uTHEGnTVYNGF9+GoiEJcHnPNhT 2v/f472O/0ApR2hSh1EDHiRAeSsiYQgDWQ== X-Google-Smtp-Source: AGRyM1tgaP3Wil2NQyveHXDIGKcOl2wqnAQzb5LbysS2GFRKjkpFtnovRSMCJJGIBkxSrbeY/9X8Uw== X-Received: by 2002:a05:6214:202f:b0:432:4810:1b34 with SMTP id 15-20020a056214202f00b0043248101b34mr15944977qvf.35.1657990430289; Sat, 16 Jul 2022 09:53:50 -0700 (PDT) Received: from [192.168.2.100] (pool-72-79-19-93.nwrknj.east.verizon.net. [72.79.19.93]) by smtp.gmail.com with ESMTPSA id c8-20020ac86608000000b0031eb6b42b26sm5486270qtp.73.2022.07.16.09.53.49 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 16 Jul 2022 09:53:49 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------JOjCZIIdY1LTqZoY1rB3TaLd" Message-ID: Date: Sat, 16 Jul 2022 12:53:49 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.11.0 Subject: Re: Proposal to introduce a shuffle function to intarray extension Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <9d160a44-7675-51e8-60cf-6d64b76db831@aboutsource.net> From: Mladen Gogala In-Reply-To: <9d160a44-7675-51e8-60cf-6d64b76db831@aboutsource.net> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------JOjCZIIdY1LTqZoY1rB3TaLd Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 7/15/22 04:36, Martin Kalcher wrote: > Dear list, > > i am dealing with an application that processes fairly large arrays of > integers. It makes heavy use of the intarray extension, which works > great in most cases. However, there are two requirements that cannot > be addressed by the extension and are rather slow with plain SQL. Both > can be met with shuffling: > > - Taking n random members from an integer array > - Splitting an array into n chunks, where each member is assigned to a > random chunk > > Shuffling is currently implemented by unnesting the array, ordering > the members by random() and aggregating them again. Martin, have you considered PL/Python and NumPy module? -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------JOjCZIIdY1LTqZoY1rB3TaLd Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
On 7/15/22 04:36, Martin Kalcher wrote:
Dear list,

i am dealing with an application that processes fairly large arrays of integers. It makes heavy use of the intarray extension, which works great in most cases. However, there are two requirements that cannot be addressed by the extension and are rather slow with plain SQL. Both can be met with shuffling:

- Taking n random members from an integer array
- Splitting an array into n chunks, where each member is assigned to a random chunk

Shuffling is currently implemented by unnesting the array, ordering the members by random() and aggregating them again.


Martin, have you considered PL/Python and NumPy module?

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------JOjCZIIdY1LTqZoY1rB3TaLd--