X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 310A447626E for ; Tue, 4 Mar 2003 13:17:12 -0500 (EST) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with SMTP id 58799-03 for ; Tue, 4 Mar 2003 13:17:10 -0500 (EST) Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by postgresql.org (Postfix) with ESMTP id 5EC8D47623F for ; Tue, 4 Mar 2003 13:17:08 -0500 (EST) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.9.3/8.9.3) with ESMTP id VAA16683; Tue, 4 Mar 2003 21:16:42 +0300 (MSK) Date: Tue, 4 Mar 2003 21:16:41 +0300 (MSK) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Greg Stark Cc: Achilleus Mantzios , pgsql-sql Subject: Re: Gist indexes on int arrays In-Reply-To: <87k7ffkr3o.fsf@stark.dyndns.tv> Message-ID: References: <87smu3kv7d.fsf@stark.dyndns.tv> <87k7ffkr3o.fsf@stark.dyndns.tv> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new X-Archive-Number: 200303/120 X-Sequence-Number: 12160 On Tue, 4 Mar 2003, Greg Stark wrote: > > 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. what's the time to create gist indices separately ? I suppose 15m is the time to create btree index on *single* column ? > > -- > greg > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83