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 1oCoIf-0007Ew-Kh for pgsql-general@arkaria.postgresql.org; Sat, 16 Jul 2022 20:22:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oCoIe-0000sO-A5 for pgsql-general@arkaria.postgresql.org; Sat, 16 Jul 2022 20:22:04 +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 1oCoId-0000rT-Ty for pgsql-general@lists.postgresql.org; Sat, 16 Jul 2022 20:22:03 +0000 Received: from mout-b-105.mailbox.org ([2001:67c:2050:102:465::105]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oCoIb-0007IS-Gn for pgsql-general@lists.postgresql.org; Sat, 16 Jul 2022 20:22:03 +0000 Received: from smtp1.mailbox.org (smtp1.mailbox.org [10.196.197.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange ECDHE (P-384) server-signature RSA-PSS (4096 bits) server-digest SHA256) (No client certificate requested) by mout-b-105.mailbox.org (Postfix) with ESMTPS id 4LlflS4lZsz9sl9; Sat, 16 Jul 2022 22:21:56 +0200 (CEST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aboutsource.net; s=MBO0001; t=1658002916; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=Sih4UeZx7gSYDxQKzIuptiDaSoXxrMuOB2bCuEqX7O0=; b=NZSKy9Yo4aGsfIFWQQ1GKHM/U62BycfxCaiatUZ3WwZ4+ZDq2UGfQ/r9YtkHcoPGuvYDfy upH0C87tyDFf6ElZTCkPTKhf4hqYGRv7ZGo6lcFL1xKe1DLwyw6IKSNINOg80YttLey+z/ 5GHFp1T/MnhUyGhkirw1v6QhxIbXH2WclSHbVJhO1z7lztDPpZ/Couo0yb6ijZzj2Z9Nr0 J9it7o/9P8+gROf5GXvJAT/Q/Lf5xkvsZmpGjnxK44hpQ+M3wEq4iIXhZsTCA2758RKQ/d BIA8GIDpU6DKKZQWgc9kWyE8SF1fdXV8QtHpWJwRRouKSmO29LElNh8p4EpQAA== Message-ID: <7933c4a5-0e72-889e-1be9-e3574ffe333b@aboutsource.net> Date: Sat, 16 Jul 2022 22:21:54 +0200 MIME-Version: 1.0 Content-Language: de-DE To: Mladen Gogala , pgsql-general@lists.postgresql.org References: <9d160a44-7675-51e8-60cf-6d64b76db831@aboutsource.net> From: Martin Kalcher Subject: Re: Proposal to introduce a shuffle function to intarray extension In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Am 16.07.22 um 18:53 schrieb Mladen Gogala: > 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? 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