MIME-Version: 1.0
Date: Mon, 2 Nov 2015 09:52:33 +0300
Message-ID: 
 <CAEB3-FhsZxnFVeSD5Ahxph8TdmJh+Aw9n-RLLaFk6CNyb+MW4A@mail.gmail.com>
Subject: GIN index always doing Re-check condition, postgres 9.1
From: Andrey Osenenko <andrey.osenenko@gmail.com>
To: pgsql-performance@postgresql.org
Content-Type: multipart/alternative; boundary=001a1143c552c3376605238938fc
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a1143c552c3376605238938fc
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

I have a table with roughly 300 000 rows, each row containing two large
strings - one is news article HTML, the other is news article plaintext.
The table has a bigint primary key.

A GIN index is used to do fulltext search on the plaintext part. All I want
to retrieve when I do fulltext search is values of primary key column.

With a popular word, the amount of results from fulltext search query can
be pretty high - a query can return 23 000 rows and some can more, and will
return more as the database continues to grow.

The problem I have is that postgres always does Re-check condition step for
my request. That query with 23k rows takes 20 seconds to execute, and
EXPLAIN shows that almost all of that time is spent
re-checking condition. The second time I run the same query, I get results
immediately.
What this means is that every time user does a search for some word no one
searched before, he has to wait a very long time, which is unacceptable for
us.

Is this happening by design, or am I doing something wrong? The way I see
it, since docs say GIN indexes are lossless, the database should be able to
fetch just primary key values for matching rows for me.

Here's schema, query, explain and database version:

CREATE TABLE kard_md.fulldata
(
  id_iu bigint NOT NULL,
  url character varying NOT NULL,
  original text,
  edited text,
  plaintext text,
  date timestamp without time zone,
  CONSTRAINT fulldata_pkey PRIMARY KEY (id_iu)
);

CREATE INDEX fulldata_plaintext_idx
  ON kard_md.fulldata
  USING gin
  (to_tsvector('russian'::regconfig, plaintext));


EXPLAIN (ANALYZE, BUFFERS) select id_iu from kard_md.fulldata where
to_tsvector('russian',fulldata.plaintext) @@
plainto_tsquery('russian','=D0=BC=D0=BE=D1=81=D0=BA=D0=B2=D0=B0');

1st run:
Bitmap Heap Scan on fulldata  (cost=3D266.79..39162.57 rows=3D23069 width=
=3D8)
(actual time=3D135.727..19499.667 rows=3D23132 loops=3D1)
  Recheck Cond: (to_tsvector('russian'::regconfig, plaintext) @@
'''=D0=BC=D0=BE=D1=81=D0=BA=D0=B2'''::tsquery)
  Buffers: shared hit=3D115 read=3D13000
  ->  Bitmap Index Scan on fulldata_plaintext_idx  (cost=3D0.00..261.02
rows=3D23069 width=3D0) (actual time=3D104.834..104.834 rows=3D23132 loops=
=3D1)
        Index Cond: (to_tsvector('russian'::regconfig, plaintext) @@
'''=D0=BC=D0=BE=D1=81=D0=BA=D0=B2'''::tsquery)
        Buffers: shared hit=3D3 read=3D21
Total runtime: 19512.479 ms

2nd run:
Bitmap Heap Scan on fulldata  (cost=3D266.79..39162.57 rows=3D23069 width=
=3D8)
(actual time=3D25.423..48.649 rows=3D23132 loops=3D1)
  Recheck Cond: (to_tsvector('russian'::regconfig, plaintext) @@
'''=D0=BC=D0=BE=D1=81=D0=BA=D0=B2'''::tsquery)
  Buffers: shared hit=3D13115
  ->  Bitmap Index Scan on fulldata_plaintext_idx  (cost=3D0.00..261.02
rows=3D23069 width=3D0) (actual time=3D18.057..18.057 rows=3D23132 loops=3D=
1)
        Index Cond: (to_tsvector('russian'::regconfig, plaintext) @@
'''=D0=BC=D0=BE=D1=81=D0=BA=D0=B2'''::tsquery)
        Buffers: shared hit=3D24
Total runtime: 49.612 ms

select version()
'PostgreSQL 9.1.15, compiled by Visual C++ build 1500, 64-bit'

--001a1143c552c3376605238938fc
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div><div><div><div><div>I have a table with roughly 300 0=
00 rows, each row containing two large strings - one is news article HTML, =
the other is news article plaintext. The table has a bigint primary key.<br=
><br></div>A GIN index is used to do fulltext search on the plaintext part.=
 All I want to retrieve when I do fulltext search is values of primary key =
column.<br><br></div>With a popular word, the amount of results from fullte=
xt search query can be pretty high - a query can return 23 000 rows and som=
e can more, and will return more as the database continues to grow.<br><br>=
The problem I have is that postgres always does Re-check condition step for=
 my request. That query with 23k rows takes 20 seconds to execute, and EXPL=
AIN shows that almost all of that time is spent<br></div>re-checking condit=
ion. The second time I run the same query, I get results immediately.<br></=
div><div>What this means is that every time user does a search for some wor=
d no one searched before, he has to wait a very long time, which is unaccep=
table for us.<br></div><div><br></div>Is this happening by design, or am I =
doing something wrong? The way I see it, since docs say GIN indexes are los=
sless, the database should be able to fetch just primary key values for mat=
ching rows for me.<br><br></div>Here&#39;s schema, query, explain and datab=
ase version:<br><div><br>CREATE TABLE kard_md.fulldata<br>(<br>=C2=A0 id_iu=
 bigint NOT NULL,<br>=C2=A0 url character varying NOT NULL,<br>=C2=A0 origi=
nal text,<br>=C2=A0 edited text,<br>=C2=A0 plaintext text,<br>=C2=A0 date t=
imestamp without time zone,<br>=C2=A0 CONSTRAINT fulldata_pkey PRIMARY KEY =
(id_iu)<br>);<br><br>CREATE INDEX fulldata_plaintext_idx<br>=C2=A0 ON kard_=
md.fulldata<br>=C2=A0 USING gin<br>=C2=A0 (to_tsvector(&#39;russian&#39;::r=
egconfig, plaintext));<br><br><br>EXPLAIN (ANALYZE, BUFFERS) select id_iu f=
rom kard_md.fulldata where to_tsvector(&#39;russian&#39;,fulldata.plaintext=
) @@ plainto_tsquery(&#39;russian&#39;,&#39;=D0=BC=D0=BE=D1=81=D0=BA=D0=B2=
=D0=B0&#39;);<br><br>1st run:<br>Bitmap Heap Scan on fulldata=C2=A0 (cost=
=3D266.79..39162.57 rows=3D23069 width=3D8) (actual time=3D135.727..19499.6=
67 rows=3D23132 loops=3D1)<br>=C2=A0 Recheck Cond: (to_tsvector(&#39;russia=
n&#39;::regconfig, plaintext) @@ &#39;&#39;&#39;=D0=BC=D0=BE=D1=81=D0=BA=D0=
=B2&#39;&#39;&#39;::tsquery)<br>=C2=A0 Buffers: shared hit=3D115 read=3D130=
00<br>=C2=A0 -&gt;=C2=A0 Bitmap Index Scan on fulldata_plaintext_idx=C2=A0 =
(cost=3D0.00..261.02 rows=3D23069 width=3D0) (actual time=3D104.834..104.83=
4 rows=3D23132 loops=3D1)<br>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Ind=
ex Cond: (to_tsvector(&#39;russian&#39;::regconfig, plaintext) @@ &#39;&#39=
;&#39;=D0=BC=D0=BE=D1=81=D0=BA=D0=B2&#39;&#39;&#39;::tsquery)<br>=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers: shared hit=3D3 read=3D21<br>Tota=
l runtime: 19512.479 ms<br><br>2nd run:<br>Bitmap Heap Scan on fulldata=C2=
=A0 (cost=3D266.79..39162.57 rows=3D23069 width=3D8) (actual time=3D25.423.=
.48.649 rows=3D23132 loops=3D1)<br>=C2=A0 Recheck Cond: (to_tsvector(&#39;r=
ussian&#39;::regconfig, plaintext) @@ &#39;&#39;&#39;=D0=BC=D0=BE=D1=81=D0=
=BA=D0=B2&#39;&#39;&#39;::tsquery)<br>=C2=A0 Buffers: shared hit=3D13115<br=
>=C2=A0 -&gt;=C2=A0 Bitmap Index Scan on fulldata_plaintext_idx=C2=A0 (cost=
=3D0.00..261.02 rows=3D23069 width=3D0) (actual time=3D18.057..18.057 rows=
=3D23132 loops=3D1)<br>=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 Index Con=
d: (to_tsvector(&#39;russian&#39;::regconfig, plaintext) @@ &#39;&#39;&#39;=
=D0=BC=D0=BE=D1=81=D0=BA=D0=B2&#39;&#39;&#39;::tsquery)<br>=C2=A0=C2=A0=C2=
=A0=C2=A0=C2=A0=C2=A0=C2=A0 Buffers: shared hit=3D24<br>Total runtime: 49.6=
12 ms<br><br>select version()<br>&#39;PostgreSQL 9.1.15, compiled by Visual=
 C++ build 1500, 64-bit&#39;<br><br><br></div></div>

--001a1143c552c3376605238938fc--