Received: from maia.hub.org (maia-3.hub.org [200.46.204.243])
	by mail.postgresql.org (Postfix) with ESMTP id 6F60AB5DC00
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  2 Aug 2011 07:03:49 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia,
	port 10024) with ESMTP id 75440-07
	for <pgsql-performance-postgresql.org@mail.postgresql.org>;
	Tue,  2 Aug 2011 10:03:42 +0000 (UTC)
X-Greylist: delayed 03:40:47.450015 by SQLgrey-1.7.6
Received: from tur.go2.pl (tur.go2.pl [193.17.41.50])
	by mail.postgresql.org (Postfix) with ESMTP id F344BB5DBCB
	for <pgsql-performance@postgresql.org>;
	Tue,  2 Aug 2011 07:03:41 -0300 (ADT)
Received: from moh1-ve3.go2.pl (moh1-ve3.go2.pl [193.17.41.134])
	by tur.go2.pl (Postfix) with ESMTP id 88BEE24F7FC
	for <pgsql-performance@postgresql.org>;
	Tue,  2 Aug 2011 08:22:57 +0200 (CEST)
Received: from moh1-ve3.go2.pl (unknown [10.0.0.134])
	by moh1-ve3.go2.pl (Postfix) with ESMTP id A3CD36644A4
	for <pgsql-performance@postgresql.org>;
	Tue,  2 Aug 2011 08:22:37 +0200 (CEST)
Received: from o2.pl (unknown [10.0.0.140])
	by moh1-ve3.go2.pl (Postfix) with SMTP
	for <pgsql-performance@postgresql.org>;
	Tue,  2 Aug 2011 08:22:37 +0200 (CEST)
Subject: =?UTF-8?Q?Tsearch2_-_bad_performance_with_concatenated?=
	=?UTF-8?Q?_ts-vectors?=
From: =?UTF-8?Q?Jan_Wielgus?= <jan_w@tlen.pl>
To: pgsql-performance@postgresql.org
Mime-Version: 1.0
Message-ID: <3479994c.402ea88e.4e3797ac.972cd@tlen.pl>
Date: Tue, 02 Aug 2011 08:22:36 +0200
X-Originator: 87.99.33.98
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.889 tagged_above=-10 required=5
	tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_NONE=-0.0001,
	T_TO_NO_BRKTS_FREEMAIL=0.01
X-Spam-Level: 
X-Archive-Number: 201108/19
X-Sequence-Number: 44466

Hello=20everyone,

This=20is=20my=20first=20post=20on=20this=20list,=20I=20tried=20to=20look=
=20after=20possible=20solutions=20in=20the=20archive,=20as=20well=20as=20=
in=20google,=20but=20I=20could=20not=20find=20an=20explanation=20for=20su=
ch=20a=20specific=20situation.

I=20am=20facing=20a=20performance=20problem=20connected=20with=20Postgres=
=20Tsearch2=20FTS=20mechanism.

Here=20is=20my=20query:

select=20participant.participant=5Fid=20from=20participant=20participant
join=20person=20person=20on=20person.person=5Fparticipant=5Fid=20=3D=20pa=
rticipant.participant=5Fid
left=20join=20registration=20registration=20on=20registration.registratio=
n=5Fregistered=5Fparticipant=5Fid=20=3D=20participant.participant=5Fid
left=20join=20enrollment=20enrollment=20on=20registration.registration=5F=
enrollment=5Fid=20=3D=20enrollment.enrollment=5Fid
join=20registration=5Fconfiguration=20registration=5Fconfiguration=20on=20=
enrollment.enrollment=5Fconfiguration=5Fid=20=3D=20registration=5Fconfigu=
ration.configuration=5Fid
left=20join=20event=5Fcontext=20context=20on=20context.context=5Fid=20=3D=
=20registration=5Fconfiguration.configuration=5Fcontext=5Fid=20
where=20participant.participant=5Ftype=20=3D=20'PERSON'
and=20participant=5Fstatus=20=3D=20'ACTIVE'
and=20context.context=5Fcode=20in=20('GB2TST2010A')=09=09=09=09
and=20registration=5Fconfiguration.configuration=5Ftype=20in=20('VISITOR'=
)
and=20registration=5Fconfiguration.configuration=5Fid=20is=20not=20null
and=20participant.participant=5Ftsv=20||=20person.person=5Ftsv=20@@=20to=5F=
tsquery('simple',to=5Ftsquerystring('Abigail'))
limit=20100

As=20you=20see,=20I=20am=20using=20two=20vectors=20which=20I=20concatenat=
e=20and=20check=20against=20a=20tsquery.=20

Both=20vectors=20are=20indexed=20with=20GIN=20and=20updated=20with=20resp=
ective=20triggers=20in=20the=20following=20way:

ALTER=20TABLE=20person=20ALTER=20COLUMN=20person=5Ftsv=20SET=20STORAGE=20=
EXTENDED;=20
CREATE=20INDEX=20person=5Fft=5Findex=20ON=20person=20USING=20gin(person=5F=
tsv);=20
CREATE=20OR=20REPLACE=20FUNCTION=20update=5Fperson=5Ftsv()=20RETURNS=20tr=
igger=20AS=20$$=20BEGIN=20NEW.person=5Ftsv=20:=3D=20to=5Ftsvector('simple=
',create=5Ftsv(=20ARRAY[NEW.person=5Ffirst=5Fname,=20NEW.person=5Flast=5F=
name,=20NEW.person=5Fmiddle=5Fname]=20));=20RETURN=20NEW;=20END;=20$$=20L=
ANGUAGE=20'plpgsql';
CREATE=20TRIGGER=20person=5Ftsv=5Fupdate=20BEFORE=20INSERT=20or=20UPDATE=20=
ON=20person=20FOR=20EACH=20ROW=20EXECUTE=20PROCEDURE=20update=5Fperson=5F=
tsv();

ALTER=20TABLE=20participant=20ALTER=20COLUMN=20participant=5Ftsv=20SET=20=
STORAGE=20EXTENDED;=20
CREATE=20INDEX=20participant=5Fft=5Findex=20ON=20participant=20USING=20gi=
n(participant=5Ftsv);=20
CREATE=20OR=20REPLACE=20FUNCTION=20update=5Fparticipant=5Ftsv()=20RETURNS=
=20trigger=20AS=20$$=20BEGIN=20NEW.participant=5Ftsv=20:=3D=20to=5Ftsvect=
or('simple',create=5Ftsv(=20ARRAY[NEW.participant=5Flogin,=20NEW.particip=
ant=5Femail]=20));=20RETURN=20NEW;=20END;=20$$=20LANGUAGE=20'plpgsql';
CREATE=20TRIGGER=20participant=5Ftsv=5Fupdate=20BEFORE=20INSERT=20or=20UP=
DATE=20ON=20participant=20FOR=20EACH=20ROW=20EXECUTE=20PROCEDURE=20update=
=5Fparticipant=5Ftsv();

The=20database=20is=20quite=20big=20-=20has=20almost=20one=20million=20of=
=20participant=20records.=20The=20above=20query=20has=20taken=20almost=20=
67=20seconds=20to=20execute=20and=20fetch=20100=20rows,=20which=20is=20un=
acceptable=20for=20us.

As=20I=20assume,=20the=20problem=20is,=20when=20the=20vectors=20are=20con=
catenated,=20the=20individual=20indexes=20for=20each=20vector=20are=20not=
=20used.=20The=20execution=20plan=20done=20after=201st=20execution=20of=20=
the=20query:

"Limit=20=20(cost=3D46063.13..93586.79=20rows=3D100=20width=3D4)=20(actua=
l=20time=3D4963.620..39703.645=20rows=3D100=20loops=3D1)"
"=20=20->=20=20Nested=20Loop=20=20(cost=3D46063.13..493736.04=20rows=3D94=
2=20width=3D4)=20(actual=20time=3D4963.617..39703.349=20rows=3D100=20loop=
s=3D1)"
"=20=20=20=20=20=20=20=20Join=20Filter:=20(registration=5Fconfiguration.c=
onfiguration=5Fid=20=3D=20enrollment.enrollment=5Fconfiguration=5Fid)"
"=20=20=20=20=20=20=20=20->=20=20Nested=20Loop=20=20(cost=3D46063.13..493=
662.96=20rows=3D3769=20width=3D8)=20(actual=20time=3D4963.517..39701.557=20=
rows=3D159=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Nested=20Loop=20=20(co=
st=3D46063.13..466987.33=20rows=3D3769=20width=3D8)=20(actual=20time=3D49=
63.498..39698.542=20rows=3D159=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Hash=
=20Join=20=20(cost=3D46063.13..430280.76=20rows=3D4984=20width=3D8)=20(ac=
tual=20time=3D4963.464..39692.676=20rows=3D216=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Hash=20Cond:=20(participant.participant=5Fid=20=3D=20person.person=5F=
participant=5Fid)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Join=20Filter:=20((participant.participant=5Ftsv=20||=20person.pers=
on=5Ftsv)=20@@=20to=5Ftsquery('simple'::regconfig,=20to=5Ftsquerystring('=
Abigail'::text)))"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20->=20=20Seq=20Scan=20on=20participant=20=20(cost=3D0.00..84680.85=20=
rows=3D996741=20width=3D42)=20(actual=20time=3D0.012..3132.944=20rows=3D1=
007151=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20Filter:=20(((participant=5Ftype)::text=20=3D=20'P=
ERSON'::text)=20AND=20((participant=5Fstatus)::text=20=3D=20'ACTIVE'::tex=
t))"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20->=20=20Hash=20=20(cost=3D25495.39..25495.39=20rows=3D1012539=20wid=
th=3D38)=20(actual=20time=3D3145.628..3145.628=20rows=3D1007151=20loops=3D=
1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20Buckets:=202048=20=20Batches:=20128=20=20Memory=20=
Usage:=20556kB"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20->=20=20Seq=20Scan=20on=20person=20=20(cost=3D0.0=
0..25495.39=20rows=3D1012539=20width=3D38)=20(actual=20time=3D0.062..1582=
.990=20rows=3D1007151=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Inde=
x=20Scan=20using=20idx=5Fregistration=5Fregistered=5Fparticipant=5Fid=20o=
n=20registration=20=20(cost=3D0.00..7.35=20rows=3D1=20width=3D8)=20(actua=
l=20time=3D0.018..0.019=20rows=3D1=20loops=3D216)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Index=20Cond:=20(registration.registration=5Fregistered=5Fparticipa=
nt=5Fid=20=3D=20person.person=5Fparticipant=5Fid)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Index=20Scan=20using=20=
enrollment=5Fpkey=20on=20enrollment=20=20(cost=3D0.00..7.07=20rows=3D1=20=
width=3D8)=20(actual=20time=3D0.011..0.013=20rows=3D1=20loops=3D159)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Index=20Cond=
:=20(enrollment.enrollment=5Fid=20=3D=20registration.registration=5Fenrol=
lment=5Fid)"
"=20=20=20=20=20=20=20=20->=20=20Materialize=20=20(cost=3D0.00..16.55=20r=
ows=3D1=20width=3D4)=20(actual=20time=3D0.002..0.005=20rows=3D2=20loops=3D=
159)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Nested=20Loop=20=20(co=
st=3D0.00..16.55=20rows=3D1=20width=3D4)=20(actual=20time=3D0.056..0.077=20=
rows=3D2=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Join=20Filte=
r:=20(registration=5Fconfiguration.configuration=5Fcontext=5Fid=20=3D=20c=
ontext.context=5Fid)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Inde=
x=20Scan=20using=20idx=5Fconfiguration=5Ftype=20on=20registration=5Fconfi=
guration=20=20(cost=3D0.00..8.27=20rows=3D1=20width=3D8)=20(actual=20time=
=3D0.018..0.022=20rows=3D3=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Index=20Cond:=20((configuration=5Ftype)::text=20=3D=20'VISITOR'::te=
xt)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Filter:=20(configuration=5Fid=20IS=20NOT=20NULL)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Inde=
x=20Scan=20using=20idx=5Fevent=5Fcontext=5Fcode=20on=20event=5Fcontext=20=
context=20=20(cost=3D0.00..8.27=20rows=3D1=20width=3D4)=20(actual=20time=3D=
0.008..0.010=20rows=3D1=20loops=3D3)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Index=20Cond:=20((context.context=5Fcode)::text=20=3D=20'GB2TST2010=
A'::text)"
"Total=20runtime:=2039775.578=20ms"

The=20assumption=20seems=20to=20be=20correct,=20no=20indexes=20on=20vecto=
rs=20are=20used=20-=20sequence=20scans=20are=20done=20instead:

Join=20Filter:=20((participant.participant=5Ftsv=20||=20person.person=5Ft=
sv)=20@@=20to=5Ftsquery('simple'::regconfig,=20to=5Ftsquerystring('Abigai=
l'::text)))"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20->=20=20Seq=20Scan=20on=20participant=20=20(cost=3D0.00..84680.85=20=
rows=3D996741=20width=3D42)=20(actual=20time=3D0.012..3132.944=20rows=3D1=
007151=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20Filter:=20(((participant=5Ftype)::text=20=3D=20'P=
ERSON'::text)=20AND=20((participant=5Fstatus)::text=20=3D=20'ACTIVE'::tex=
t))"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20->=20=20Hash=20=20(cost=3D25495.39..25495.39=20rows=3D1012539=20wid=
th=3D38)=20(actual=20time=3D3145.628..3145.628=20rows=3D1007151=20loops=3D=
1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20Buckets:=202048=20=20Batches:=20128=20=20Memory=20=
Usage:=20556kB"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20->=20=20Seq=20Scan=20on=20person=20=20(cost=3D0.0=
0..25495.39=20rows=3D1012539=20width=3D38)=20(actual=20time=3D0.062..1582=
.990=20rows=3D1007151=20loops=3D1)"


After=20I=20removed=20one=20of=20the=20vectors=20from=20the=20query=20and=
=20used=20only=20a=20single=20vector=20
...
and=20person.person=5Ftsv=20@@=20to=5Ftsquery('simple',=20to=5Ftsquery('s=
imple',to=5Ftsquerystring('Abigail'))
...
then=20the=20execution=20was=20much=20faster=20-=20about=205=20seconds

Plan=20afterwards:

"Limit=20=20(cost=3D41.14..8145.82=20rows=3D100=20width=3D4)=20(actual=20=
time=3D3.776..13.454=20rows=3D100=20loops=3D1)"
"=20=20->=20=20Nested=20Loop=20=20(cost=3D41.14..21923.77=20rows=3D270=20=
width=3D4)=20(actual=20time=3D3.773..13.248=20rows=3D100=20loops=3D1)"
"=20=20=20=20=20=20=20=20->=20=20Nested=20Loop=20=20(cost=3D41.14..19730.=
17=20rows=3D270=20width=3D8)=20(actual=20time=3D3.760..11.971=20rows=3D10=
0=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20Join=20Filter:=20(registration=
=5Fconfiguration.configuration=5Fid=20=3D=20enrollment.enrollment=5Fconfi=
guration=5Fid)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Nested=20Loop=20=20(co=
st=3D0.00..16.55=20rows=3D1=20width=3D4)=20(actual=20time=3D0.051..0.051=20=
rows=3D1=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20Join=20Filte=
r:=20(registration=5Fconfiguration.configuration=5Fcontext=5Fid=20=3D=20c=
ontext.context=5Fid)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Inde=
x=20Scan=20using=20idx=5Fconfiguration=5Ftype=20on=20registration=5Fconfi=
guration=20=20(cost=3D0.00..8.27=20rows=3D1=20width=3D8)=20(actual=20time=
=3D0.020..0.022=20rows=3D2=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Index=20Cond:=20((configuration=5Ftype)::text=20=3D=20'VISITOR'::te=
xt)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Filter:=20(configuration=5Fid=20IS=20NOT=20NULL)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Inde=
x=20Scan=20using=20idx=5Fevent=5Fcontext=5Fcode=20on=20event=5Fcontext=20=
context=20=20(cost=3D0.00..8.27=20rows=3D1=20width=3D4)=20(actual=20time=3D=
0.008..0.009=20rows=3D1=20loops=3D2)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Index=20Cond:=20((context.context=5Fcode)::text=20=3D=20'GB2TST2010=
A'::text)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Nested=20Loop=20=20(co=
st=3D41.14..19700.12=20rows=3D1080=20width=3D12)=20(actual=20time=3D3.578=
..11.431=20rows=3D269=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Nest=
ed=20Loop=20=20(cost=3D41.14..12056.27=20rows=3D1080=20width=3D12)=20(act=
ual=20time=3D3.568..8.203=20rows=3D269=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20->=20=20Bitmap=20Heap=20Scan=20on=20person=20=20(cost=3D41.14..3687=
.07=20rows=3D1080=20width=3D4)=20(actual=20time=3D3.553..4.401=20rows=3D3=
46=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20Recheck=20Cond:=20(person=5Ftsv=20@@=20to=5Ftsque=
ry('simple'::regconfig,=20to=5Ftsquerystring('Abigail'::text)))"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20->=20=20Bitmap=20Index=20Scan=20on=20person=5Fft=5F=
index=20=20(cost=3D0.00..40.87=20rows=3D1080=20width=3D0)=20(actual=20tim=
e=3D3.353..3.353=20rows=3D1060=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20Index=20Cond:=20(person=5Ftsv=20=
@@=20to=5Ftsquery('simple'::regconfig,=20to=5Ftsquerystring('Abigail'::te=
xt)))"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20->=20=20Index=20Scan=20using=20idx=5Fregistration=5Fregistered=5Fpa=
rticipant=5Fid=20on=20registration=20=20(cost=3D0.00..7.74=20rows=3D1=20w=
idth=3D8)=20(actual=20time=3D0.006..0.007=20rows=3D1=20loops=3D346)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20Index=20Cond:=20(registration.registration=5Fregi=
stered=5Fparticipant=5Fid=20=3D=20person.person=5Fparticipant=5Fid)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20->=20=20Inde=
x=20Scan=20using=20enrollment=5Fpkey=20on=20enrollment=20=20(cost=3D0.00.=
.7.07=20rows=3D1=20width=3D8)=20(actual=20time=3D0.006..0.007=20rows=3D1=20=
loops=3D269)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20Index=20Cond:=20(enrollment.enrollment=5Fid=20=3D=20registration.re=
gistration=5Fenrollment=5Fid)"
"=20=20=20=20=20=20=20=20->=20=20Index=20Scan=20using=20participant=5Fpke=
y=20on=20participant=20=20(cost=3D0.00..8.11=20rows=3D1=20width=3D4)=20(a=
ctual=20time=3D0.007..0.009=20rows=3D1=20loops=3D100)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20Index=20Cond:=20(participant.p=
articipant=5Fid=20=3D=20person.person=5Fparticipant=5Fid)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20Filter:=20(((participant.parti=
cipant=5Ftype)::text=20=3D=20'PERSON'::text)=20AND=20((participant.partic=
ipant=5Fstatus)::text=20=3D=20'ACTIVE'::text))"
"Total=20runtime:=2013.858=20ms"

Now=20the=20index=20on=20vector=20was=20used:

"Recheck=20Cond:=20(person=5Ftsv=20@@=20to=5Ftsquery('simple'::regconfig,=
=20to=5Ftsquerystring('Abigail'::text)))"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20->=20=20Bitmap=20Index=20Scan=20on=20person=5Fft=5F=
index=20=20(cost=3D0.00..40.87=20rows=3D1080=20width=3D0)=20(actual=20tim=
e=3D3.353..3.353=20rows=3D1060=20loops=3D1)"
"=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=
=20=20=20=20=20=20=20=20=20=20=20=20=20=20Index=20Cond:=20(person=5Ftsv=20=
@@=20to=5Ftsquery('simple'::regconfig,=20to=5Ftsquerystring('Abigail'::te=
xt)))"

So,=20there=20is=20apparently=20a=20problem=20with=20vector=20concatenati=
ng=20-=20the=20indexes=20don't=20work=20then.=20I=20tried=20to=20use=20th=
e=20vectors=20separately=20and=20to=20make=20'OR'=20comparison=20between=20=
single=20vector=20@@=20ts=5Fquery=20checks,
but=20it=20didn't=20help=20very=20much=20(performance=20was=20better,=20b=
ut=20still=20over=2020=20sec):
...
(participant.participant=5Ftsv=20@@=20to=5Ftsquery('simple',to=5Ftsquerys=
tring('Abigail')))=20OR=20(person.person=5Ftsv=20@@=20to=5Ftsquery('simpl=
e',to=5Ftsquerystring('Abigail')))=20
...

Is=20there=20a=20way=20to=20make=20this=20work=20with=20better=20performa=
nce?=20Or=20is=20it=20necessary=20to=20create=20a=20single=20vector=20tha=
t=20contains=20data=20from=20multiple=20tables=20and=20then=20add=20an=20=
index=20on=20it?=20It=20would=20be=20so=20far=20problematic=20for=20us,
because=20we=20are=20using=20multiple=20complex=20queries=20with=20variab=
le=20number=20of=20selected=20columns.=20I=20know=20that=20another=20solu=
tion=20might=20be=20an=20union=20among=20multiple=20queries,=20every=20of=
=20which=20uses=20a=20single=20vector,
but=20this=20solution=20is=20inconvenient=20too.

Greetings

Jan