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