X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6A0C2476154 for ; Tue, 4 Mar 2003 11:56:43 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 35519-01 for ; Tue, 4 Mar 2003 11:56:41 -0500 (EST) Received: from sabre.velocet.net (sabre.velocet.net [216.138.209.205]) by postgresql.org (Postfix) with ESMTP id 214FB4761EC for ; Tue, 4 Mar 2003 11:56:40 -0500 (EST) Received: from stark.dyndns.tv (H162.C233.tor.velocet.net [216.138.233.162]) by sabre.velocet.net (Postfix) with ESMTP id 474AA1380D0; Tue, 4 Mar 2003 11:56:45 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.dyndns.tv ident=foobar) by stark.dyndns.tv with smtp (Exim 3.36 #1 (Debian)) id 18qFie-0007vs-00; Tue, 04 Mar 2003 11:56:44 -0500 To: Greg Stark Cc: Achilleus Mantzios , Greg Stark , pgsql-sql Subject: Re: Gist indexes on int arrays References: <87smu3kv7d.fsf@stark.dyndns.tv> In-Reply-To: <87smu3kv7d.fsf@stark.dyndns.tv> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 04 Mar 2003 11:56:43 -0500 Message-ID: <87k7ffkr3o.fsf@stark.dyndns.tv> Lines: 27 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/117 X-Sequence-Number: 12157 Greg Stark writes: > Can I have a GiST index on (foo_id, attribute_set_array) and have it be just > as fast at narrowing the search to just foo_id = 900 but also speed up the ~ > operation? Hm, so if I understand what I'm reading I can do this if I load the btree_gist contrib module as well. I'm still not sure whether it'll be worthwhile for this application though. I have a bit of a problem though. Is building GiST indexes supposed to take much much longer than building btree indexes? It's been running nearly an hour and it's still going. The hard drive is hardly moving so it seems to be all cpu usage. I don't even see any pgsql_tmp usage. db=# CREATE INDEX cache_gist_idx on cache using gist ( foo_id , attribute_set gist__int_ops); postgres 30176 86.3 22.2 64896 57344 ? R 11:08 40:32 postgres: postgres slo [local] CREATE INDEX I don't remember exact numbers but building the normal btree index took on the order of 15m. This will have to be rebuilt nightly, an hour long index build won't be practical. -- greg