Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Xu2M7-0006xu-Vp for pgsql-sql@arkaria.postgresql.org; Thu, 27 Nov 2014 16:55:48 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Xu2M7-0007UJ-DK for pgsql-sql@arkaria.postgresql.org; Thu, 27 Nov 2014 16:55:47 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1Xu2M6-0007UC-D8 for pgsql-sql@postgresql.org; Thu, 27 Nov 2014 16:55:46 +0000 Received: from mail-wi0-x22b.google.com ([2a00:1450:400c:c05::22b]) by magus.postgresql.org with esmtps (TLS1.0:RSA_AES_256_CBC_SHA1:256) (Exim 4.80) (envelope-from ) id 1Xu2Lz-0003KR-2e for pgsql-sql@postgresql.org; Thu, 27 Nov 2014 16:55:45 +0000 Received: by mail-wi0-f171.google.com with SMTP id bs8so16313082wib.10 for ; Thu, 27 Nov 2014 08:55:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:cc:subject:references :in-reply-to:content-type:content-transfer-encoding; bh=SqfOPphgKMZlMxrdD6r8t8BeK4qzJ1JOEL7g1Oe03jk=; b=ujZABZVvi5LMT87hRaKFHcQ9Nl1pJ+BXmDao2PY5jpocOdTB3qouPI65OlO6xvsXEd 5ilmmKnW6aWPu+XUsPUMnaxxfLES5tPGjPYLICk8yDBy+siOK4GYSvEEI8t3qcHNdSkm OHv2FafpRDqFicADZbXyngLilL3FewXxbd9sXup83DtbwBeTZ5u3BROWqVX7J3h1ZjIl uQB6GdT3bF1vrH25NkVBEW8FRQNbg726y6a51nr+B39/fNSNpuK9APoKJmHbLvIzyNrl k2gM2PdYJ8y7fDHWspZ9IRydMJkpziPlc+oswEJgA3kTdPOmra796qZcfx8IGuE/kE8x InQA== X-Received: by 10.194.190.19 with SMTP id gm19mr60728180wjc.51.1417107338168; Thu, 27 Nov 2014 08:55:38 -0800 (PST) Received: from timbomac.home (host86-158-181-242.range86-158.btcentralplus.com. [86.158.181.242]) by mx.google.com with ESMTPSA id bx19sm13222955wib.6.2014.11.27.08.55.36 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Thu, 27 Nov 2014 08:55:37 -0800 (PST) Message-ID: <54775787.4030801@gmail.com> Date: Thu, 27 Nov 2014 16:55:35 +0000 From: Tim Dudgeon User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:24.0) Gecko/20100101 Thunderbird/24.6.0 MIME-Version: 1.0 CC: pgsql-sql@postgresql.org Subject: Re: Querying with arrays References: <547722A7.4040702@gmail.com> <3676.1417100070@sss.pgh.pa.us> In-Reply-To: <3676.1417100070@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 1.1 (+) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org On 27/11/2014 14:54, Tom Lane wrote: > You could create a GIN index on lists.hits and then do > > SELECT t1.* > FROM some_table t1 > JOIN lists l ON array[t1.id] <@ l.hits > WHERE l.id = 2; > > How efficient that will be remains to be determined though; > if the l.id condition will eliminate a lot of matches it > could still be kind of slow. Afraid that was *much* slower. Tim -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql