Re: Postgresql 8.1.4 - performance issues for select on

От: Jim C. Nasby
Тема: Re: Postgresql 8.1.4 - performance issues for select on
Дата: ,
Msg-id: 20061018223551.GD56874@nasby.net
(см: обсуждение, исходный текст)
Ответ на: Re: Postgresql 8.1.4 - performance issues for select on  (Jeff Davis)
Ответы: Re: Postgresql 8.1.4 - performance issues for select on  (Jeff Davis)
Re: Postgresql 8.1.4 - performance issues for select on  (Ioana Danes)
Список: pgsql-performance

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

Postgresql 8.1.4 - performance issues for select on view using max  (Ioana Danes, )
 Re: Postgresql 8.1.4 - performance issues for select on view using max  (Dimitri Fontaine, )
  Re: Postgresql 8.1.4 - performance issues for select on view using max  (Ioana Danes, )
   Re: Postgresql 8.1.4 - performance issues for select on view using max  (Ioana Danes, )
    Re: Postgresql 8.1.4 - performance issues for select on view using max  (Dimitri Fontaine, )
     Re: Postgresql 8.1.4 - performance issues for select on  (Jeff Davis, )
      Re: Postgresql 8.1.4 - performance issues for select on  ("Jim C. Nasby", )
       Re: Postgresql 8.1.4 - performance issues for select on  (Jeff Davis, )
        Re: Postgresql 8.1.4 - performance issues for select on  ("Jim C. Nasby", )
         Re: Postgresql 8.1.4 - performance issues for select on  (Jeff Davis, )
         Re: Postgresql 8.1.4 - performance issues for select on  (Ioana Danes, )
        Re: Postgresql 8.1.4 - performance issues for select on  (Tom Lane, )
         Re: Postgresql 8.1.4 - performance issues for select on  (Ioana Danes, )
      Re: Postgresql 8.1.4 - performance issues for select on view using max  (Ioana Danes, )
 Re: Postgresql 8.1.4 - performance issues for select on view using max  ("Joshua Marsh", )
 Re: Postgresql 8.1.4 - performance issues for select on  (Jeff Davis, )

On Wed, Oct 18, 2006 at 03:32:15PM -0700, Jeff Davis wrote:
> On Wed, 2006-10-18 at 17:10 -0500, Jim C. Nasby wrote:
> > Sorry, don't have the earlier part of this thread, but what about...
> >
> > SELECT greatest(max(a), max(b)) ...
> >
> > ?
>
> To fill you in, we're trying to get the max of a union (a view across
> two physical tables).

UNION or UNION ALL? You definitely don't want to do a plain UNION if you
can possibly avoid it.

> It can be done if you're creative with the query; I suggested a query
> that selected the max of the max()es of the individual tables. Your
> query could work too. However, the trick would be getting postgresql to
> recognize that it can transform "SELECT max(x) FROM foo" into that,
> where foo is a view of a union.
>
> If PostgreSQL could sort the result of a union by merging the results of
> two index scans, I think the problem would be solved. Is there something
> preventing this, or is it just something that needs to be added to the
> planner?

Hrm... it'd be worth trying the old ORDER BY ... LIMIT 1 trick just to
see if that worked in this case, but I don't have much hope for that.
--
Jim Nasby                                            
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)


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

От: Jeff Davis
Дата:
Сообщение: Re: Postgresql 8.1.4 - performance issues for select on
От: Mark Kirkwood
Дата:
Сообщение: Re: measuring shared memory usage on Windows