To: Oleg Bartunov <oleg@sai.msu.su>
Cc: Greg Stark <gsstark@mit.edu>,
	Achilleus Mantzios <achill@matrix.gatewaynet.com>,
	pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Gist indexes on int arrays
References: <Pine.LNX.4.44.0303040934040.26498-100000@matrix.gatewaynet.com>
	<87smu3kv7d.fsf@stark.dyndns.tv> <87k7ffkr3o.fsf@stark.dyndns.tv>
	<Pine.GSO.4.53.0303042110580.15273@ra.sai.msu.su>
In-Reply-To: <Pine.GSO.4.53.0303042110580.15273@ra.sai.msu.su>
From: Greg Stark <gsstark@mit.edu>
Organization: The Emacs Conspiracy; member since 1992
Date: 04 Mar 2003 13:38:15 -0500
Message-ID: <87znobj7u0.fsf@stark.dyndns.tv>
Lines: 25
User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Oleg Bartunov <oleg@sai.msu.su> writes:

> > db=# CREATE INDEX cache_gist_idx on cache using gist ( foo_id , attribute_set gist__int_ops);

> what's the time to create gist indices separately ? I suppose 15m is the time
> to create btree index on *single* column ?

Unfortunately I can't run timing tests on it without invalidating whatever
timing results I get for the current index build which is still running.

postgres 30176 93.7 21.7 68964 56096 ?       R    11:08 133:39 postgres: postgres slo [local] CREATE INDEX

15m was what i remember as the time to build a unique (non-gist) btree index
on (foo_id,bar_id) where foo_id is the same integer column as the leading
column in the gist index, and bar_id is another integer column.

There are 1,161,435 records, with 384 distinct values of foo_id. The number of
records per value of foo_id ranges from 1 to 11,474.

The annoying thing here is that 99% of the attribute_set columns will have
exactly one value in them. All this work is for the 1% that can have multiple
values.

--
greg