Re: Tsearch2 - bad performance with concatenated ts-vectors

Поиск
Список
Период
Сортировка
От Kevin Grittner
Тема Re: Tsearch2 - bad performance with concatenated ts-vectors
Дата
Msg-id 4E3AC5F8020000250003FAFF@gw.wicourts.gov
обсуждение исходный текст
Ответ на Re: Tsearch2 - bad performance with concatenated ts-vectors  (Gavin Flower <GavinFlower@archidevsys.co.nz>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: Postgres 8.4 memory related parameters
Следующее
От: Scott Marlowe
Дата:
Сообщение: Re: Suspected Postgres Datacorruption