Re: Tsearch2 - bad performance with concatenated ts-vectors

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