Re: Planner issue on sorting joining of two tables with limit

От: Tom Lane
Тема: Re: Planner issue on sorting joining of two tables with limit
Дата: ,
Msg-id: 5252.1272300692@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Planner issue on sorting joining of two tables with limit  (Коротков Александр)
Ответы: Re: Planner issue on sorting joining of two tables with limit  (Alexander Korotkov)
Список: pgsql-performance

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

Planner issue on sorting joining of two tables with limit  (Коротков Александр, )
 Re: Planner issue on sorting joining of two tables with limit  (Tom Lane, )
  Re: Planner issue on sorting joining of two tables with limit  (Alexander Korotkov, )
   Re: Planner issue on sorting joining of two tables with limit  (Alexander Korotkov, )
    Re: Planner issue on sorting joining of two tables with limit  ("Kevin Grittner", )
     Re: Planner issue on sorting joining of two tables with limit  (Tom Lane, )
      Re: Planner issue on sorting joining of two tables with limit  (Robert Haas, )

=?KOI8-R?B?68/Sz9TLz9cg4czFy9PBzsTS?= <> writes:
> So PostgreSQL planner can produce the plan I need but it doesn't produce
> this plan when I specify particular second ordering column.

Well, no, because that plan wouldn't produce the specified ordering;
or at least it would be a lucky coincidence if it did.  It's only
sorting on t1.value.

> So is there any
> way to make planner produce desired plan when particular second ordering
> column is specified?

Not when the ordering columns come from two different tables.  (If they
were in the same table then scanning a two-column index could produce
the demanded sort order.)  I don't see any way to satisfy this query
without an explicit sort step, which means it has to look at the whole
join output.

If you're willing to make assumptions like "the required 10 rows will be
within the first 100 t1.value rows" then you could nest an ORDER BY
t1.value LIMIT 100 query inside something that did an ORDER BY with both
columns.  But this fails if you have too many duplicate t1.value values,
and your test case suggests that you might have a lot of them.  In any
case it would stop being fast if you make the inner LIMIT very large.

            regards, tom lane


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

От: Anj Adu
Дата:
Сообщение: tmpfs and postgres memory
От: Greg Spiegelberg
Дата:
Сообщение: Re: tmpfs and postgres memory