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 B17FDD1B220 for ; Mon, 26 Jul 2004 17:40:47 -0300 (ADT) 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 08615-01 for ; Mon, 26 Jul 2004 20:40:42 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id E8E42D1B215 for ; Mon, 26 Jul 2004 17:40:36 -0300 (ADT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1BpCGu-0002Pn-00; Mon, 26 Jul 2004 16:40:32 -0400 To: "Ross J. Reedstrom" Cc: Greg Stark , Merlin Moncure , pgsql-performance@postgresql.org Subject: Re: arrays and indexes References: <6EE64EF3AB31D5448D0007DD34EEB34101AEF1@Herge.rcsinc.local> <20040726045710.GA14892@cnx.rice.edu> <87acxnwaev.fsf@stark.xeocode.com> <20040726184703.GA27820@cnx.rice.edu> In-Reply-To: <20040726184703.GA27820@cnx.rice.edu> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 26 Jul 2004 16:40:32 -0400 Message-ID: <87y8l6v6wv.fsf@stark.xeocode.com> Lines: 42 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/182 X-Sequence-Number: 7573 "Ross J. Reedstrom" writes: > These groups _really are_ ideal for Joe Conway's work on arrays: we need > ordered vectors, so we'd be sorting all the time, otherwise. They're > static, and they're read only. The one thing they're not is fixed, known > size (Sorry Merlin). They work fine for the query as shown: the only > issue is performance. Well just as long as you understand the trade-offs. Denormalizing can be useful but you need to know what flexibility you're losing too. > > Postgres does have a way to do what you ask, though. It involves GiST > > indexes and the operators from the contrib/intarray directory from the > > Postgres source. > > Well, yes, that's how it used to be done. I figured the new array > support should be able to handle it without the addon, however. I think you can btree index arrays now, which is new, but it's not useful for the kind of lookup you're doing. It would only be useful for joining on array types or looking for groups with given content, or things like that. > > However I warn you in advance that this is fairly esoteric stuff and > > will take some time to get used to. And at least in my case I found > > the indexes didn't actually help much for my data sets, probably > > because they just weren't big enough to benefit. > > I know that they should help in this case: we've got lots of content. > Any particular author or maintainter will be in a small fraction of > those. i.e.: it's ideal for an index. And the current joined case uses > an index, when it's available. I'll take a look at the GiST/contrib work, > anyway. I would be curious to know how it goes. My own project uses denormalized sets stored as arrays as well, though in my case they're precalculated from the fully normalized data. I tried to use GiST indexes but ran into problems combining the btree-GiST code with array GiST code in a multicolumn index. I still don't really know why it failed, but after two days building the index I gave up. -- greg