X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 91A4E9DCC91 for ; Sat, 21 Jan 2006 13:29:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 08227-03 for ; Sat, 21 Jan 2006 13:29:08 -0400 (AST) Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id 6D9EE9DC801 for ; Sat, 21 Jan 2006 13:29:06 -0400 (AST) Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by svr4.postgresql.org (Postfix) with ESMTP id 073985AF87D for ; Sat, 21 Jan 2006 15:24:06 +0000 (GMT) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.13.4/8.13.4) with ESMTP id k0LFMqpE008425; Sat, 21 Jan 2006 18:23:02 +0300 (MSK) Date: Sat, 21 Jan 2006 18:22:52 +0300 (MSK) From: Oleg Bartunov X-X-Sender: megera@ra.sai.msu.su To: Martijn van Oosterhout Cc: Tom Lane , Ron , pgsql-performance@postgresql.org Subject: Re: [GENERAL] Creation of tsearch2 index is very slow In-Reply-To: <20060121150424.GB9955@svana.org> Message-ID: References: <26153.1137784469@sss.pgh.pa.us> <20060120201036.GE31908@svana.org> <26583.1137788505@sss.pgh.pa.us> <20060120205132.GF31908@svana.org> <26958.1137791955@sss.pgh.pa.us> <20060120213754.GG31908@svana.org> <7.0.1.0.2.20060120174141.039b03e8@earthlink.net> <6111.1137797436@sss.pgh.pa.us> <20060121130830.GA9955@svana.org> <20060121150424.GB9955@svana.org> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.384 required=5 tests=[AWL=-0.095, DNS_FROM_RFC_ABUSE=0.479] X-Spam-Score: 0.384 X-Spam-Level: X-Archive-Number: 200601/394 X-Sequence-Number: 16872 On Sat, 21 Jan 2006, Martijn van Oosterhout wrote: > On Sat, Jan 21, 2006 at 04:29:13PM +0300, Oleg Bartunov wrote: >> Martijn, you're right! We want not only to split page to very >> different parts, but not to increase the number of sets bits in >> resulted signatures, which are union (OR'ed) of all signatures >> in part. We need not only fast index creation (thanks, Tom !), >> but a better index. Some information is available here >> http://www.sai.msu.su/~megera/oddmuse/index.cgi/Tsearch_V2_internals >> There are should be more detailed document, but I don't remember where:) > > I see how it works, what I don't quite get is whether the "inverted > index" you refer to is what we're working with here, or just what's in > tsearchd? just tsearchd. We plan to implement inverted index into PostgreSQL core and then adapt tsearch2 to use it as option for read-only archives. > >>> That's harder though (this algorithm does approximate it sort of) >>> and I havn't come up with an algorithm yet >> >> Don't ask how hard we thought :) > > Well, looking at how other people are struggling with it, it's > definitly a Hard Problem. One thing though, I don't think the picksplit > algorithm as is really requires you to strictly have the longest > distance, just something reasonably long. So I think the alternate > algorithm I posted should produce equivalent results. No idea how to > test it though... you may try our development module 'gevel' to see how dense is a signature. www=# \d v_pages Table "public.v_pages" Column | Type | Modifiers -----------+-------------------+----------- tid | integer | not null path | character varying | not null body | character varying | title | character varying | di | integer | dlm | integer | de | integer | md5 | character(22) | fts_index | tsvector | Indexes: "v_pages_pkey" PRIMARY KEY, btree (tid) "v_pages_path_key" UNIQUE, btree (path) "v_gist_key" gist (fts_index) # select * from gist_print('v_gist_key') as t(level int, valid bool, a gtsvector) where level =1; level | valid | a -------+-------+-------------------------------- 1 | t | 1698 true bits, 318 false bits 1 | t | 1699 true bits, 317 false bits 1 | t | 1701 true bits, 315 false bits 1 | t | 1500 true bits, 516 false bits 1 | t | 1517 true bits, 499 false bits (5 rows) Regards, Oleg _____________________________________________________________ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83