From: Tom Lane <tgl@sss.pgh.pa.us>
To: Коротков Александр <aekorotkov@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Planner issue on sorting joining of two tables with limit
Date: Mon, 26 Apr 2010 12:51:32 -0400
Message-ID: <5252.1272300692@sss.pgh.pa.us> (raw)
In-Reply-To: <k2j2e2e4b141004251122u7adab639ve203afe7a486673f@mail.gmail.com>
References: <k2j2e2e4b141004251122u7adab639ve203afe7a486673f@mail.gmail.com>
=?KOI8-R?B?68/Sz9TLz9cg4czFy9PBzsTS?= <aekorotkov@gmail.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, aekorotkov@gmail.com
Subject: Re: Planner issue on sorting joining of two tables with limit
In-Reply-To: <5252.1272300692@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox