Re: Help with tuning this query

От: Ken Egervari
Тема: Re: Help with tuning this query
Дата: ,
Msg-id: 002801c51f59$9c043900$6a01a8c0@KEN
(см: обсуждение, исходный текст)
Ответ на: Help with tuning this query  ("Ken Egervari")
Список: pgsql-performance

Скрыть дерево обсуждения

Help with tuning this query  ("Ken Egervari", )
 Re: Help with tuning this query  (Richard Huxton, )
  Re: Help with tuning this query  (Bricklen Anderson, )
   Re: Help with tuning this query  (Bruce Momjian, )
 Re: Help with tuning this query  (John Arbash Meinel, )
 Re: Help with tuning this query  ("Ken Egervari", )
  Re: Help with tuning this query  (John Arbash Meinel, )
 Re: Help with tuning this query  (Ragnar Hafstað, )
 Re: Help with tuning this query  ("Ken Egervari", )
  Re: Help with tuning this query  (Ragnar Hafstað, )
 Re: Help with tuning this query  ("Ken Egervari", )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Tom Lane, )
   Re: Help with tuning this query (with explain analyze finally)  (John A Meinel, )
  Re: Help with tuning this query (with explain analyze finally)  (Josh Berkus, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Tom Lane, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
 Re: Help with tuning this query (more musings)  ("Ken Egervari", )
  Re: Help with tuning this query (more musings)  (John A Meinel, )
 Re: Help with tuning this query (Some musings)  ("Ken Egervari", )
 Re: Help with tuning this query  (Mark Kirkwood, )
 Re: Help with tuning this query  (Josh Berkus, )
 Re: Help with tuning this query (more musings)  ("Ken Egervari", )
  Re: Help with tuning this query (more musings)  (Richard Huxton, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Josh Berkus, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (John Arbash Meinel, )
  Re: Help with tuning this query (with explain analyze finally)  (Josh Berkus, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken Egervari", )
  Re: Help with tuning this query (with explain analyze finally)  (Richard Huxton, )
  Re: Help with tuning this query (with explain analyze finally)  (John Arbash Meinel, )
 Re: Help with tuning this query (with explain analyze finally)  ("Ken", )
  Re: Help with tuning this query (with explain analyze finally)  (John Arbash Meinel, )
   Re: Help with tuning this query (with explain analyze finally)  (John A Meinel, )

>> left join is for eager loading so that I don't have to run a seperate
>> query
>> to fetch the children for each shipment.  This really does improve
>> performance because otherwise you'll have to make N+1 queries to the
>> database, and that's just too much overhead.
>
> are you saying that you are actually doing a
>  select s.*,ss.* ...
> ?

Yes, this is how the SQL should be written.  When I manually converted the
query, I forgot to include this detail.  In hibernate, you don't need to
specifiy the ss.* because you are dealing with objects, so you just say
shipment.  The ss.* is indicated in the "fetch" part of the Hibernate query.
That was my mistake.

> it might help the planner estimate better the number of cs rows
> affected. whether this improves performance depends on whether
> the best plans are sensitive to this.

This sounds like a good idea since cs rows are quite large.  shipment and
shipment_status are the largest tables in the database and they will grow
very large over time.



В списке pgsql-performance по дате сообщения:

От: John A Meinel
Дата:
Сообщение: Re: Help with tuning this query (with explain analyze finally)
От: "Ken Egervari"
Дата:
Сообщение: Re: Help with tuning this query (with explain analyze finally)