From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: 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: Thu, 04 Aug 2011 16:16:56 -0500
Message-ID: <4E3AC5F8020000250003FAFF@gw.wicourts.gov> (raw)
In-Reply-To: <4E3881E6.7050501@archidevsys.co.nz>
References: <3479994c.402ea88e.4e3797ac.972cd@tlen.pl>
<4E3881E6.7050501@archidevsys.co.nz>
Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
> 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.
> So I would like people's opinions on how I have reformatted [...]
This is a little off-topic for a performance list, but I have to
admit a lot of sympathy, since I have to do the same for similar
reasons. I found that keeping operators near the left margin and
always having matching parentheses be on the same line or in the
same column helps me tremendously. (This style tends not to be so
popular for younger coders with stronger eyes.)
I tend to go more this way:
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
;
With multiple ON conditions I like to do this:
JOIN some_table st
ON ( st.this_column = ot.this_column
AND st.another_column = ot.another_column
AND ( st.feathers > ot.feathers
OR st.lead_ingots > ot.lead_ingots
)
)
I often line up the operators and column-names, too, if it seems
easier on the eyes.
The above won't look very sensible in a proportional font.
I doubt this will become a dominant coding convention; it's just
what works for me. ;-)
-Kevin
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: Kevin.Grittner@wicourts.gov, GavinFlower@archidevsys.co.nz, jan_w@tlen.pl
Subject: Re: Tsearch2 - bad performance with concatenated ts-vectors
In-Reply-To: <4E3AC5F8020000250003FAFF@gw.wicourts.gov>
* 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