X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5A20A329F24 for ; Fri, 8 Oct 2004 10:11:24 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41833-04 for ; Fri, 8 Oct 2004 09:11:18 +0000 (GMT) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.196]) by svr1.postgresql.org (Postfix) with ESMTP id 65AC932AF62 for ; Fri, 8 Oct 2004 10:11:17 +0100 (BST) Received: by mproxy.gmail.com with SMTP id 77so6816rnk for ; Fri, 08 Oct 2004 02:11:16 -0700 (PDT) Received: by 10.38.65.39 with SMTP id n39mr48739rna; Fri, 08 Oct 2004 02:11:16 -0700 (PDT) Received: by 10.38.163.22 with HTTP; Fri, 8 Oct 2004 02:11:16 -0700 (PDT) Message-ID: <758d5e7f041008021163fb40eb@mail.gmail.com> Date: Fri, 8 Oct 2004 11:11:16 +0200 From: Dawid Kuroczko Reply-To: Dawid Kuroczko To: pgsql-performance@postgresql.org Subject: integer[] indexing. Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.9 tagged_above=0.0 required=5.0 tests=FROM_ENDS_IN_NUMS X-Spam-Level: X-Archive-Number: 200410/96 X-Sequence-Number: 8572 I have a large table with a column: ids integer[] not null most of these entries (over 95%) contain only one array element, some can contain up to 10 array elements. seqscan is naturally slow. GIST on int_array works nice, but GIST isn't exactly a speed daemon when it comes to updating. So I thought, why not create partial indexes? CREATE INDEX one_element_array_index ON table ((ids[1])) WHERE icount(ids) <= 1; CREATE INDEX many_element_array_index ON table USING GIST (ids) WHERE icount(ids) > 1; Now, if I select WHERE icount(ids) <= 1 AND ids[1] = 33 I get lightning fast results. If I select WHERE icount(ids) > 1 AND ids && '{33}' -- I get them even faster. But when I phrase the query: SELECT * FROM table WHERE (icount(ids) <= 1 AND ids[1] = 33) OR (icount(ids) > 1 AND ids && '{33}'); Planner insists on using seqscan. Even with enable_seqscan = off; Any hints, comments? :) [ I think thsese partial indexes take best of two worlds, only if planner wanted to take advantage of it... :) ] Regards, Dawid