From: Gavin Flower <GavinFlower@archidevsys.co.nz>
To: Jan Wielgus <jan_w@tlen.pl>
Cc: pgsql-performance@postgresql.org
Subject: Re: Tsearch2 - bad performance with concatenated ts-vectors
Date: Wed, 03 Aug 2011 11:01:58 +1200
Message-ID: <4E3881E6.7050501@archidevsys.co.nz> (raw)
In-Reply-To: <3479994c.402ea88e.4e3797ac.972cd@tlen.pl>
References: <3479994c.402ea88e.4e3797ac.972cd@tlen.pl>
On 02/08/11 18:22, Jan Wielgus wrote:
> select participant.participant_id from participant participant
> join person person on person.person_participant_id = participant.participant_id
> left join registration registration on registration.registration_registered_participant_id = participant.participant_id
> left join enrollment enrollment on registration.registration_enrollment_id = enrollment.enrollment_id
> join registration_configuration registration_configuration on enrollment.enrollment_configuration_id = registration_configuration.configuration_id
> left join event_context context on context.context_id = registration_configuration.configuration_context_id
> where participant.participant_type = 'PERSON'
> and participant_status = 'ACTIVE'
> and context.context_code in ('GB2TST2010A')
> and registration_configuration.configuration_type in ('VISITOR')
> and registration_configuration.configuration_id is not null
> and participant.participant_tsv || person.person_tsv @@ to_tsquery('simple',to_tsquerystring('Abigail'))
> limit 100
I am experimenting with formatting styles, especially relating to
joins. Because I have poor eyesight: visual clues are important, so
that I can focus on key points. Hence the use of abbreviations, naming
conventions, and careful indenting. (I found this especially
important, when I had to write a stored procedure with some 3K lines of
Sybase TransactSQL!) I also use uppercase key words, but I have not
bothered here.
So I would like people's opinions on how I have reformatted the above.
select
participant.participant_id
from
participant pa
join person pe
on pe.person_participant_id = pa.participant_id
left join registration re
on re.registration_registered_participant_id = pa.participant_id
left join enrollment en
on re.registration_enrollment_id = en.enrollment_id
join registration_configuration rc
on en.enrollment_configuration_id = rc.configuration_id
left join event_context ec
on ec.context_id = rc.configuration_context_id
where
pa.participant_type = 'PERSON' and
pa.participant_status = 'ACTIVE' and
ec.context_code in ('GB2TST2010A') and
rc.configuration_type in ('VISITOR') and
rc.configuration_id is not null and
pa.participant_tsv || pe.person_tsv @@
to_tsquery('simple',to_tsquerystring('Abigail'))
limit 100
Cheers,
Gavin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: GavinFlower@archidevsys.co.nz, jan_w@tlen.pl
Subject: Re: Tsearch2 - bad performance with concatenated ts-vectors
In-Reply-To: <4E3881E6.7050501@archidevsys.co.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox