Re: Planner issue on sorting joining of two tables with limit - Mailing list pgsql-performance

From Tom Lane
Subject Re: Planner issue on sorting joining of two tables with limit
Date
Msg-id 5252.1272300692@sss.pgh.pa.us
Whole thread Raw
In response to Planner issue on sorting joining of two tables with limit  (Коротков Александр)
Responses Re: Planner issue on sorting joining of two tables with limit  (Alexander Korotkov)
List pgsql-performance
=?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 by date:

Previous
From: Коротков Александр
Date:
Subject: Planner issue on sorting joining of two tables with limit
Next
From: Alvaro Herrera
Date:
Subject: Re: autovacuum strategy / parameters