Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1atHju-0001QM-0R for pgsql-performance@arkaria.postgresql.org; Thu, 21 Apr 2016 16:46:02 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1atHjt-0006Q8-1z for pgsql-performance@arkaria.postgresql.org; Thu, 21 Apr 2016 16:46:01 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1atHjs-0006Q2-MK for pgsql-performance@postgresql.org; Thu, 21 Apr 2016 16:46:00 +0000 Received: from mail-lf0-x234.google.com ([2a00:1450:4010:c07::234]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1atHjp-0000gx-PX for pgsql-performance@postgresql.org; Thu, 21 Apr 2016 16:46:00 +0000 Received: by mail-lf0-x234.google.com with SMTP id c126so64373055lfb.2 for ; Thu, 21 Apr 2016 09:45:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc; bh=eofhA34jSsb+I4OeymNlc5H4fNNSCsF78cYAJEgIi0k=; b=IHYXC1bDpk9lfle8Ah3AC1pD2KefmAZfoSiewv4d3fBazIrbVAt/hQNHQcgKt+j1GI a+iFg2/3BPBeh/cHJX9mSA4WWeuFmkDg2dy2R3Pi2BDp7592dlQ6N168h3rBtSxOBJ3O 37NoYZ8swulGdmfO1V89kNAKI/fu0UbLefJrDSfJLsybGBpLVr8LDNxxVcKEX3aDI+42 5L/v6ZGO6r0wp5KJYaETVSVJxPM8qSKV1H+bp76eUKdDbBenIISxE7AKSkut1CPGuFm7 VWLItr3FqnH3L0nnrHRNX0C83ebdPCKdJPyT2EJhz8WUOGxjPRrpfDkQRhVARPg1ioLa YB/A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:in-reply-to:references:date :message-id:subject:from:to:cc; bh=eofhA34jSsb+I4OeymNlc5H4fNNSCsF78cYAJEgIi0k=; b=XzA3f8qO72IubtKxLdZ4nbL0kSmtv7uBE3FhUHyuKu4/Rl5Rmn5rLjF89jBiUbJYuQ QCSLIQlKeXh+CNbdyxzTCh5V9/NulVx4//ADWkGY/WJ2GKHg142I0nJ1ZN1xLfEBVIeC eNE7pgsJNAV7n91DJA/8f76N/gaVM78v+XYsmdXmedsEK7mdOaxSOXYmQEHVsIAbgeaa y/BX9WBnXAcraixME8M0l5ZNDaZLdMmd/v267rsuTMTcbpid5B5TwCpj8Fpw+erOgli9 lg+5/9X/4fszLyyCSHf4EUGeN7+nLC2DPNeyzGBjyYAijrlRuw6d7trMohT+aNJeFoE3 fshA== X-Gm-Message-State: AOPr4FU9EsR8aK/6DHuCv8R7tQU1lFprJIy+gYbSaPA9Kf3zy2Wa+aKO3s/LWZvZWGpqWnEPzEtMBdwRkAw5Cw== MIME-Version: 1.0 X-Received: by 10.25.153.5 with SMTP id b5mr5913570lfe.122.1461257156888; Thu, 21 Apr 2016 09:45:56 -0700 (PDT) Received: by 10.25.19.28 with HTTP; Thu, 21 Apr 2016 09:45:56 -0700 (PDT) In-Reply-To: <5717D07E.3070802@sigaev.ru> References: <5717D07E.3070802@sigaev.ru> Date: Thu, 21 Apr 2016 09:45:56 -0700 Message-ID: Subject: Re: Performant queries on table with many boolean columns From: Jeff Janes To: Teodor Sigaev Cc: Rob Imig , "pgsql-performance@postgresql.org" Content-Type: text/plain; charset=UTF-8 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org On Wed, Apr 20, 2016 at 11:54 AM, Teodor Sigaev wrote: >> >> The obvious thing seems to make a table with ~100 columns, with 1 column >> for each boolean property. Though, what type of indexing strategy would >> one use on that table? Doesn't make sense to do BTREE. Is there a better >> way to structure it? >> > looks like a deal for contrib/bloom index in upcoming 9.6 release Not without doing a custom compilation with an increased INDEX_MAX_KEYS: ERROR: cannot use more than 32 columns in an index But even so, I'm skeptical this would do better than a full scan. It would be interesting to test that. Cheers, Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance