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

=?KOI8-R?B?68/Sz9TLz9cg4czFy9PBzsTS?= <aekorotkov(at)gmail(dot)com> 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