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 07AEFD1B26C for ; Tue, 27 Jul 2004 01:32:48 -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 88237-04 for ; Tue, 27 Jul 2004 04:32:45 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 6A4F8D1B266 for ; Tue, 27 Jul 2004 01:32:45 -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 1BpJdl-0003Kc-00; Tue, 27 Jul 2004 00:32:37 -0400 To: Tom Lane Cc: Greg Stark , "Ross J. Reedstrom" , 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> <87y8l6v6wv.fsf@stark.xeocode.com> <9847.1090876872@sss.pgh.pa.us> In-Reply-To: <9847.1090876872@sss.pgh.pa.us> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 27 Jul 2004 00:32:37 -0400 Message-ID: <87ekmyul22.fsf@stark.xeocode.com> Lines: 32 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/187 X-Sequence-Number: 7578 Tom Lane writes: > > I still don't really know why it failed, but after two days building the > > index I gave up. > > Sounds like a bug to me. Could you put together a test case? At the time I contacted one of the GiST authors and we went over things for a while. They diagnosed the problem as being caused by having a poor selectivity GiST btree as the leading column in the index. He seemed to think this was fairly fundamental and wasn't something they were going to be able to address. And I was fairly certain I didn't want to turn the index upside down to have the more selective columns first (as is usually normal) for various reasons. So I gave it up as a lost cause. In any case in my application it was unlikely to really help. I expect that leading btree index to narrow the search to only a few hundred or few thousand records in the normal case. So the access times are already within reason even having to dig through all the records. And since other queries are likely to need other records from that set I'll need them all in cache eventually. There are a lot of array columns to search through, so the added i/o to read all those indexes would probably be a net loss when they push other things out of cache. I could try setting up a test case, but I think all it took was having a btree-gist index that was insufficiently selective. In my case I had about 900 integer values each on the order of 100-1000 records. -- greg