Message-ID: <4E3881E6.7050501@archidevsys.co.nz>
Date: Wed, 03 Aug 2011 11:01:58 +1200
From: Gavin Flower <GavinFlower@archidevsys.co.nz>
Organization: ArchiDevSys
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110621 Fedora/3.1.11-1.fc14
	Lightning/1.0b3pre Thunderbird/3.1.11
MIME-Version: 1.0
To: Jan Wielgus <jan_w@tlen.pl>
CC: pgsql-performance@postgresql.org
Subject: Re: Tsearch2 - bad performance with concatenated ts-vectors
References: <3479994c.402ea88e.4e3797ac.972cd@tlen.pl>
In-Reply-To: <3479994c.402ea88e.4e3797ac.972cd@tlen.pl>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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