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 1oCpMj-00020f-Q3 for pgsql-general@arkaria.postgresql.org; Sat, 16 Jul 2022 21:30:21 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oCpMh-0003vn-CH for pgsql-general@arkaria.postgresql.org; Sat, 16 Jul 2022 21:30:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oCpMg-0003nQ-VN for pgsql-general@lists.postgresql.org; Sat, 16 Jul 2022 21:30:19 +0000 Received: from mail-qt1-x832.google.com ([2607:f8b0:4864:20::832]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oCpMa-0007z0-0m for pgsql-general@lists.postgresql.org; Sat, 16 Jul 2022 21:30:18 +0000 Received: by mail-qt1-x832.google.com with SMTP id l14so3896431qtv.4 for ; Sat, 16 Jul 2022 14:30:11 -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=ym2DEMWC4aRxMNnHv1VHeGZL/8qjUOqGFRmYnzsrHwE=; b=Vx0V+v6WssH2FYko/wNFUOZig02Z4++mqz/H+XpB25MpYYwx1YE48YaLqocQQxun2X NHzqpYG9+DUnGYUkjwORMTLP/+4t/u+iRAiV+T8gEFJRQITR/kPopaaPoLSIfN5YL9iw Z0xEclO/1OhiDfcmEsXiS0GIywyK+sS4owFFt4uuuSAywVBszrZlgrr8hzctTqSmQsNE WNCMlFYu62Yi3+Djmj3HzQhuLt+XjJD3sQ1LbBWUVZDAuJXMGyIbu9sEZ8OmQsx8UgkV U9l8Db+M3df6rPcfzIJOtECQjKTGaLtqfXvwS5iPqk5SVG2T2nrjtvoPrffnCol/aSb2 qfmA== 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=ym2DEMWC4aRxMNnHv1VHeGZL/8qjUOqGFRmYnzsrHwE=; b=iL/fMwhYKNki0BtZW0n8WB0JHxh+8UXfFxoJwzr4DvtlV+Ro1LfnQ+vlEV2mEgWMNC dEnzG554wU50OyKZlxAIV8jyXKOWNAYXBTQ8SJ2cd4aa9AX/1HBWax+BnqFEu2xR7QQq +nISO64PF/mVnfWoJUbQggqoK9UUh0i61OGgnY7wnM3aHR6HzPDGHSWodr/m6SwngxHH wu5++8NURfDINDuv82uMxfp/X1c62l3P/jy9JQXr8jBATMHQBYLPwjPyINHT3dJIBNgb fF/xxwUlUK57b/IWZguBnohl6ttBn+6O05fUY80zmdW5A5MlPM4VH1h5imw5HkeTQUPW 8r7A== X-Gm-Message-State: AJIora/CGjt3GhpG//kcZv8+Hh/+hV7x9cbQH5m5XERNn681L/9b8J7K QsijJUTf1oEH0RmbvuTPvVo= X-Google-Smtp-Source: AGRyM1sgcPvpxpb61CMqrC9FxZBKv+0Sd/x/rcAaEe+jzS/s75yA3m9kStpMZ3lTjFaUtsebgIp9PQ== X-Received: by 2002:a05:622a:1213:b0:31e:e114:d75b with SMTP id y19-20020a05622a121300b0031ee114d75bmr5315606qtx.316.1658007009227; Sat, 16 Jul 2022 14:30:09 -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 n20-20020ac81e14000000b003177969a48fsm6161715qtl.21.2022.07.16.14.30.08 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sat, 16 Jul 2022 14:30:08 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------NhJGCf0KBLqWFAvy4uF1F1rI" Message-ID: <1351c57e-4a5d-4554-8b15-1264ce0cb39a@gmail.com> Date: Sat, 16 Jul 2022 17:30:08 -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: Martin Kalcher , pgsql-general@lists.postgresql.org References: <9d160a44-7675-51e8-60cf-6d64b76db831@aboutsource.net> <7933c4a5-0e72-889e-1be9-e3574ffe333b@aboutsource.net> From: Mladen Gogala In-Reply-To: <7933c4a5-0e72-889e-1be9-e3574ffe333b@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. --------------NhJGCf0KBLqWFAvy4uF1F1rI Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 7/16/22 16:21, Martin Kalcher wrote: > Hey Mladen, > > thank you for your advice. Unfortunately the performance of shuffling > with NumPy is about the same as with SQL. > >   create function numpy_shuffle(arr int[]) >   returns int[] >   as $$ >     import numpy >     numpy.random.shuffle(arr) >     return arr >   $$ language 'plpython3u'; > >   select arr[1:3]::text || ' ... ' || arr[3999998:4000000]::text >   from ( >     select numpy_shuffle(arr) arr from numbers >   ) shuffled; > >   ------------------------------------------------------- >    {674026,3306457,1727170} ... {343875,3825484,1235246} > >   Time: 2315.431 ms (00:02.315) > > Am i doing something wrong? > > Martin Hi Martin, No, you're doing everything right. I have no solution for you. You may need to do some C programming or throw a stronger hardware at the problem. The performance of your processors may be the problem. Good luck! -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------NhJGCf0KBLqWFAvy4uF1F1rI Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 7/16/22 16:21, Martin Kalcher wrote:
Hey Mladen,

thank you for your advice. Unfortunately the performance of shuffling with NumPy is about the same as with SQL.

  create function numpy_shuffle(arr int[])
  returns int[]
  as $$
    import numpy
    numpy.random.shuffle(arr)
    return arr
  $$ language 'plpython3u';

  select arr[1:3]::text || ' ... ' || arr[3999998:4000000]::text
  from (
    select numpy_shuffle(arr) arr from numbers
  ) shuffled;

  -------------------------------------------------------
   {674026,3306457,1727170} ... {343875,3825484,1235246}

  Time: 2315.431 ms (00:02.315)

Am i doing something wrong?

Martin

Hi Martin,

No, you're doing everything right. I have no solution for you. You may need to do some C programming or throw a stronger hardware at the problem. The performance of your processors may be the problem. Good luck!

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