Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vownv-0003Fs-Js for pgsql-performance@arkaria.postgresql.org; Fri, 06 Dec 2013 14:54:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Vownv-0003EI-3A for pgsql-performance@arkaria.postgresql.org; Fri, 06 Dec 2013 14:54:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1Vownu-0003E4-3N for pgsql-performance@postgresql.org; Fri, 06 Dec 2013 14:54:54 +0000 Received: from nm44-vm3.bullet.mail.bf1.yahoo.com ([216.109.114.254]) by magus.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1Vownd-0002tX-2S for pgsql-performance@postgresql.org; Fri, 06 Dec 2013 14:54:53 +0000 Received: from [66.196.81.172] by nm44.bullet.mail.bf1.yahoo.com with NNFMP; 06 Dec 2013 14:54:35 -0000 Received: from [98.139.212.216] by tm18.bullet.mail.bf1.yahoo.com with NNFMP; 06 Dec 2013 14:54:35 -0000 Received: from [127.0.0.1] by omp1025.mail.bf1.yahoo.com with NNFMP; 06 Dec 2013 14:54:35 -0000 X-Yahoo-Newman-Property: ymail-3 X-Yahoo-Newman-Id: 307240.48430.bm@omp1025.mail.bf1.yahoo.com Received: (qmail 82458 invoked by uid 60001); 6 Dec 2013 14:54:35 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ymail.com; s=s1024; t=1386341675; bh=cAXOn5HGIhSvZFCfESkcQ8kw1GKIK4f2Fe/PKzjaJKU=; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=XQFLpvQd81HQFqOkWtxdLFKneDjnmou8ahG3nCSv65n0QrGDK835Q2YPK4vtdm9Z8cXM8D4U2wtq1ADkTeudbMAcz5/VG2B9ARqVxwx0DYJWtJgiAT43QU+ddMTEyk24cIMP7EMnqeCEcU4zXfadxdBLLpMFsd9ZwM8ojdywLys= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com; h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=Cabaav4rb2zw9XERIauv5pQ+9RfpJKogF18tDgm9XpYGfqZy7WggJQSs4pgfP9IrOevmwcRggPwn4q5bCZ33yTra7Mie+qIvhhPoM2obveTSihdDPfY3Jfg5D4YVhG+d8Hqlu9okRRJwoup5j5D6xyEcrP9FbFoVodCRjK0AX2c=; X-YMail-OSG: g607jvsVM1nlPEec6OVnP2nnswW.mwZTz_12CRq_uEcfwDr 3CJcPl6TSLKbCjFvQvgOh_iLSeZ04w83o4A3AnOJ6LIFXnA_lF1yIu9WuZb3 JnXSjdUGUl9adhV3WY05iJNqt2tyeTFqZ0mg1YqXgzqq4g0.zPIUp_lbH.hN EZkXqp5OdrISrC1JbX5qn4DrmlBCFDFC1WTFeueWlQiY9.s6Jvw592gNsbHZ txpqEERiSViGyMYJWNqtDRU7gP3SqXjbkmwAl3Ekzfhw5H6Zx7ZUMM11IcmG C5EzgvMNuiLX.FBqR6W_E448ka5udFIj6UnCzxSbpm3jbM65aZnfGSWxA5.5 .DbZ.AcpwoKkd2a_X3UKtpzKGXsMVhLZImy0Osw2euksEUJXqQzSz3Zt8mpd 4OPePiHt7sEomy0lBz54wdLjBTHy0fzhvoJ5_e380EqKDmF65Mp9hRUja_zs cYdiPubnBRj7Xl_nCCoalgOzk1VBse.Xt.ILx9LxAWPZzkPwzl0akCG4_US2 912rXBlAc9HcAZs7pH29IhKexac4gxGsmFMc8W1XScQQDhFfHm8g0sYcSttQ 0Vf88g4JlKvw5maW_y338qb9VITOJzXesd_vK9aWM Received: from [76.255.18.237] by web162903.mail.bf1.yahoo.com via HTTP; Fri, 06 Dec 2013 06:54:35 PST X-Rocket-MIMEInfo: 002.001, SmFuZWsgU2VuZHJvd3NraSA8amFuZWsxMkB3ZWIuZGU.IHdyb3RlOgoKPiBJIHdhbnQgdG8gcmVhbGl6ZSBhIEZ1bGwgVGV4dCBTZWFyY2ggd2l0aCB0aGUgdHNlYXJjaDIgZXh0ZW5zaW9uLgo.IEl0IHNob3VsZCBmaW5kIHNpbWlsYXIgc2VudGVuY2VzLgo.IMKgCj4gSSB1c2VkIG15IG93biB0cmlnZ2VyIHRvIHN0b3JlIHRoZSB0c3ZlY3RvciBvZiB0aGUgc2VudGVuY2VzIGFuZAo.IEkgY3JlYXRlZCBhIHVzdWFsIGdpc3QgaW5kZXggb24gdGhlbS4KPiBJIGhhdmUgdG8gdG8gdXNlIG1hbnkgT1Igc3RhdGUBMAEBAQE- X-Mailer: YahooMailWebService/0.8.169.609 References: Message-ID: <1386341675.6717.YahooMailNeo@web162903.mail.bf1.yahoo.com> Date: Fri, 6 Dec 2013 06:54:35 -0800 (PST) From: Kevin Grittner Reply-To: Kevin Grittner Subject: Re: Similarity search with the tsearch2 extension To: Janek Sendrowski , "pgsql-performance@postgresql.org" In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable X-Pg-Spam-Score: -2.0 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org Janek Sendrowski wrote: > I want to realize a Full Text Search with the tsearch2 extension. > It should find similar sentences. > =A0 > I used my own trigger to store the tsvector of the sentences and > I created a usual gist index on them. > I have to to use many OR statements with a low set of arguments, > what heavy damages the performance. > My former query looked like this: > SELECT * FROM tablename >=A0=A0 WHERE vector @@ to_tsquery('speci & tiger & total & weigh') >=A0=A0=A0=A0 AND vector @@ to_tsquery('largest & length & m & reach') >=A0=A0=A0=A0 AND vector @@ to_tsquery('3.3 & 306 & bodi & cat & kg'); I don't see any OR operators there. > And thats very slow. Are you sure it is using the index? Anyway, it is better to show an example, with EXPLAIN ANALYZE output.=A0 Here's mine, involving searches of War and Peace. test=3D# -- Create the table. test=3D# -- In reality, I would probably make tsv NOT NULL, test=3D# -- but I'm keeping the example simple... test=3D# CREATE TABLE war_and_peace test-#=A0=A0 ( test(#=A0=A0=A0=A0 lineno serial PRIMARY KEY, test(#=A0=A0=A0=A0 linetext text NOT NULL, test(#=A0=A0=A0=A0 tsv tsvector test(#=A0=A0 ); CREATE TABLE test=3D#=20 test=3D# -- Load from downloaded data into database. test=3D# COPY war_and_peace (linetext) test-#=A0=A0 FROM '/home/kgrittn/Downloads/war-and-peace.txt'; COPY 65007 test=3D#=20 test=3D# -- "Digest" data to lexemes. test=3D# UPDATE war_and_peace test-#=A0=A0 SET tsv =3D to_tsvector('english', linetext); UPDATE 65007 test=3D#=20 test=3D# -- Index the lexemes using GIN. test=3D# CREATE INDEX war_and_peace_tsv test-#=A0=A0 ON war_and_peace test-#=A0=A0 USING gin (tsv); CREATE INDEX test=3D#=20 test=3D# -- Make sure the database has statistics. test=3D# VACUUM ANALYZE war_and_peace; VACUUM test=3D#=20 test=3D# -- Find lines with "gentlemen". test=3D# EXPLAIN ANALYZE test-# SELECT * FROM war_and_peace test-#=A0=A0 WHERE tsv @@ to_tsquery('english', 'gentlemen'); =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0 ---------------------------------------------------------------------------= -------------------------------------------------- =A0Bitmap Heap Scan on war_and_peace=A0 (cost=3D12.52..240.18 rows=3D67 wid= th=3D115) (actual time=3D0.058..0.130 rows=3D84 loops=3D1) =A0=A0 Recheck Cond: (tsv @@ '''gentlemen'''::tsquery) =A0=A0 ->=A0 Bitmap Index Scan on war_and_peace_tsv=A0 (cost=3D0.00..12.50 = rows=3D67 width=3D0) (actual time=3D0.045..0.045 rows=3D84 loops=3D1) =A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: (tsv @@ '''gentlemen'''::tsquery) =A0Total runtime: 0.160 ms (5 rows) test=3D#=20 test=3D# -- Find lines with "ladies". test=3D# EXPLAIN ANALYZE test-# SELECT * FROM war_and_peace test-#=A0=A0 WHERE tsv @@ to_tsquery('english', 'ladies'); =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0 ---------------------------------------------------------------------------= ---------------------------------------------------- =A0Bitmap Heap Scan on war_and_peace=A0 (cost=3D13.39..547.24 rows=3D180 wi= dth=3D115) (actual time=3D0.062..0.215 rows=3D184 loops=3D1) =A0=A0 Recheck Cond: (tsv @@ '''ladi'''::tsquery) =A0=A0 ->=A0 Bitmap Index Scan on war_and_peace_tsv=A0 (cost=3D0.00..13.35 = rows=3D180 width=3D0) (actual time=3D0.043..0.043 rows=3D184 loops=3D1) =A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: (tsv @@ '''ladi'''::tsquery) =A0Total runtime: 0.247 ms (5 rows) test=3D#=20 test=3D# -- Find lines with "ladies" and "gentlemen". test=3D# EXPLAIN ANALYZE test-# SELECT * FROM war_and_peace test-#=A0=A0 WHERE tsv @@ to_tsquery('english', 'ladies & gentlemen'); =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0 ---------------------------------------------------------------------------= ------------------------------------------------ =A0Bitmap Heap Scan on war_and_peace=A0 (cost=3D20.00..24.01 rows=3D1 width= =3D115) (actual time=3D0.062..0.063 rows=3D1 loops=3D1) =A0=A0 Recheck Cond: (tsv @@ '''ladi'' & ''gentlemen'''::tsquery) =A0=A0 ->=A0 Bitmap Index Scan on war_and_peace_tsv=A0 (cost=3D0.00..20.00 = rows=3D1 width=3D0) (actual time=3D0.057..0.057 rows=3D1 loops=3D1) =A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: (tsv @@ '''ladi'' & ''gentlemen'''::ts= query) =A0Total runtime: 0.090 ms (5 rows) test=3D#=20 test=3D# -- Find lines with ("ladies" and "gentlemen") and ("provinces" and= "distance"). test=3D# EXPLAIN ANALYZE test-# SELECT * FROM war_and_peace test-#=A0=A0 WHERE tsv @@ to_tsquery('english', 'ladies & gentlemen') test-#=A0=A0=A0=A0 AND tsv @@ to_tsquery('english', 'provinces & distance'); =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0 ---------------------------------------------------------------------------= ------------------------------------------------ =A0Bitmap Heap Scan on war_and_peace=A0 (cost=3D36.00..40.02 rows=3D1 width= =3D115) (actual time=3D0.100..0.100 rows=3D1 loops=3D1) =A0=A0 Recheck Cond: ((tsv @@ '''ladi'' & ''gentlemen'''::tsquery) AND (tsv= @@ '''provinc'' & ''distanc'''::tsquery)) =A0=A0 ->=A0 Bitmap Index Scan on war_and_peace_tsv=A0 (cost=3D0.00..36.00 = rows=3D1 width=3D0) (actual time=3D0.095..0.095 rows=3D1 loops=3D1) =A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((tsv @@ '''ladi'' & ''gentlemen'''::t= squery) AND (tsv @@ '''provinc'' & ''distanc'''::tsquery)) =A0Total runtime: 0.130 ms (5 rows) test=3D#=20 test=3D# -- Find lines with ("ladies" or "gentlemen") and ("provinces" or "= distance"). test=3D# EXPLAIN ANALYZE test-# SELECT * FROM war_and_peace test-#=A0=A0 WHERE tsv @@ to_tsquery('english', 'ladies | gentlemen') test-#=A0=A0=A0=A0 AND tsv @@ to_tsquery('english', 'provinces | distance'); =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0 ---------------------------------------------------------------------------= ------------------------------------------------ =A0Bitmap Heap Scan on war_and_peace=A0 (cost=3D36.00..40.02 rows=3D1 width= =3D115) (actual time=3D0.043..0.043 rows=3D1 loops=3D1) =A0=A0 Recheck Cond: ((tsv @@ '''ladi'' | ''gentlemen'''::tsquery) AND (tsv= @@ '''provinc'' | ''distanc'''::tsquery)) =A0=A0 ->=A0 Bitmap Index Scan on war_and_peace_tsv=A0 (cost=3D0.00..36.00 = rows=3D1 width=3D0) (actual time=3D0.042..0.042 rows=3D1 loops=3D1) =A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: ((tsv @@ '''ladi'' | ''gentlemen'''::t= squery) AND (tsv @@ '''provinc'' | ''distanc'''::tsquery)) =A0Total runtime: 0.056 ms (5 rows) test=3D#=20 test=3D# -- Find lines with ("ladies" and "gentlemen") or ("provinces" and = "distance"). test=3D# EXPLAIN ANALYZE test-# SELECT * FROM war_and_peace test-#=A0=A0 WHERE tsv @@ to_tsquery('english', 'ladies & gentlemen') test-#=A0=A0=A0=A0=A0 OR tsv @@ to_tsquery('english', 'provinces & distance= '); =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0 QUERY PLAN=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0 ---------------------------------------------------------------------------= ------------------------------------------------------ =A0Bitmap Heap Scan on war_and_peace=A0 (cost=3D40.00..44.02 rows=3D1 width= =3D115) (actual time=3D0.080..0.080 rows=3D1 loops=3D1) =A0=A0 Recheck Cond: ((tsv @@ '''ladi'' & ''gentlemen'''::tsquery) OR (tsv = @@ '''provinc'' & ''distanc'''::tsquery)) =A0=A0 ->=A0 BitmapOr=A0 (cost=3D40.00..40.00 rows=3D1 width=3D0) (actual t= ime=3D0.076..0.076 rows=3D0 loops=3D1) =A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Bitmap Index Scan on war_and_peace_tsv=A0 (c= ost=3D0.00..20.00 rows=3D1 width=3D0) (actual time=3D0.052..0.052 rows=3D1 = loops=3D1) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: (tsv @@ '''ladi'' & = ''gentlemen'''::tsquery) =A0=A0=A0=A0=A0=A0=A0=A0 ->=A0 Bitmap Index Scan on war_and_peace_tsv=A0 (c= ost=3D0.00..20.00 rows=3D1 width=3D0) (actual time=3D0.024..0.024 rows=3D1 = loops=3D1) =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Index Cond: (tsv @@ '''provinc''= & ''distanc'''::tsquery) =A0Total runtime: 0.116 ms (8 rows) Can you provide a similar example which slows the slowness you report? =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =A0 -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --=20 Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance