Re: Postgresql 8.1.4 - performance issues for select on

От: Jeff Davis
Тема: Re: Postgresql 8.1.4 - performance issues for select on
Дата: ,
Msg-id: 1161205648.31645.262.camel@dogma.v10.wvs
(см: обсуждение, исходный текст)
Ответ на: Postgresql 8.1.4 - performance issues for select on view using max  (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, 2006-10-18 at 15:51 -0400, Ioana Danes wrote:
> Hi everyone,
> Testing some selects I know we have in the application
> I got into a scenario where my plan does not work
> without doing code change. This scenario is:
>
> select max(transid) from alltransaction;
>
> because the planner does not use the existent indexes
> on the 2 new tables: public.transaction and
> archive.transaction
>

First, the query is expanded into something like (I'm being inexact
here):

SELECT max(transid) FROM (SELECT * FROM public.transaction UNION SELECT
* FROM archive.transaction);

PostgreSQL added a hack to the max() aggregate so that, in the simple
case, it can recognize that what it really wants to do is use the index.
Using the index for an aggregate only works in special cases, like min()
and max(). What PostgreSQL actually does is to transform a query from:

SELECT max(value) FROM sometable;

Into:

SELECT value FROM sometable ORDER BY value DESC LIMIT 1;

In your case, it would need to transform the query into something more
like:

SELECT max(transid) FROM (
  SELECT transid FROM (
    SELECT transid FROM public.transaction ORDER BY transid DESC
      LIMIT 1
  ) t1
  UNION
  SELECT transid FROM (
    SELECT transid FROM archive.transaction ORDER BY transid DESC
      LIMIT 1
  ) t2
) t;

The reason for that is because PostgreSQL (apparently) isn't smart
enough to do a mergesort on the two indexes to sort the result of the
UNION. At least, I can't get PostgreSQL to sort over two UNIONed tables
using an index; perhaps I'm missing it.

Regards,
    Jeff Davis




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

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