Thread: Forcing a specific order
This may come across as a rather odd question. However, is there a way
to force PostgreSQL to return rows in a very specific order? I am
currently facing running one three-way-join query which will last 50+
seconds. Since this is a web-based application this hardly makes for a
fast browsing experience.
One solution is to break it up into more manageable pieces. If I can
specify the sort order then I can run concurrent queries and get things
settled easily. Otherwise I'll have to fetch for every row returned
and that seems messy and I would like to avoid it.
Any ideas?
Martin Foster
martin@ethereal-realms.org
On Tue, Oct 11, 2005 at 08:56:41PM -0400, Martin Foster wrote: > This may come across as a rather odd question. However, is there a way > to force PostgreSQL to return rows in a very specific order? Yes, with ORDER BY. But after reading the rest of your message, I'm not sure that's what you're after. > I am currently facing running one three-way-join query which will last 50+ > seconds. Since this is a web-based application this hardly makes for a > fast browsing experience. If you post the query, the table definitions, the EXPLAIN ANALYZE output, and your version of PostgreSQL to pgsql-performance, then somebody might be able to help tune the query. Making the query faster might just be a matter of creating the right indexes, making sure the tables are vacuumed and analyzed, and/or adjusting the server's configuration settings. > One solution is to break it up into more manageable pieces. If I can > specify the sort order then I can run concurrent queries and get things > settled easily. Otherwise I'll have to fetch for every row returned > and that seems messy and I would like to avoid it. It's not clear how you'd use a sort order to run concurrent queries, nor how that involves how many rows you'd have to fetch. Could you elaborate? But it might be better to post the query and the EXPLAIN ANALYZE output and let others take a look: let's examine the problem before thinking about a solution. -- Michael Fuhr
On Oct 12, 2005, at 9:56 , Martin Foster wrote: > This may come across as a rather odd question. However, is there > a way to force PostgreSQL to return rows in a very specific order? The SQL-standard ORDER BY clause is supported by PostgreSQL and is the usual way of specifying the order rows are returned. Does this not serve your needs? Michael Glaesemann grzm myrealbox com
Michael Fuhr wrote:
>
> It's not clear how you'd use a sort order to run concurrent queries,
> nor how that involves how many rows you'd have to fetch. Could you
> elaborate? But it might be better to post the query and the EXPLAIN
> ANALYZE output and let others take a look: let's examine the problem
> before thinking about a solution.
>
I should have been a bit more clear on the matter. Order by is useful
if you want to sort the dataset a specific way using rows. However,
what if I wanted to specify the order the rows are returned in using
information from another query?
While I could post table definitions, explain analyze information and
sample data it probably is not all that useful. Lets just say that
this three-way join is mixing a reverse index with two other tables.
So trying to mix in VERY large datasets into one another with SUM and
COUNT thrown in creates a nasty situation even if I would normally pick
up at the most 100 rows.
So my solution is to simplify matters and cut down the dataset used
almost immediately. Even with say 24 secondary queries to fetch
specific information this process is probably a lot cleaner?
Martin Foster
Creator/Designer Ethereal Realms
martin@ethereal-realms.org
Martin Foster <martin@ethereal-realms.org> writes:
> I should have been a bit more clear on the matter. Order by is useful
> if you want to sort the dataset a specific way using rows. However,
> what if I wanted to specify the order the rows are returned in using
> information from another query?
This sounds like you are trying to force a join order, not a specific
tuple order. Have you read the material about join ordering in the
performance-tips chapter?
regards, tom lane
Tom Lane wrote:
> Martin Foster <martin@ethereal-realms.org> writes:
>
>>I should have been a bit more clear on the matter. Order by is useful
>>if you want to sort the dataset a specific way using rows. However,
>>what if I wanted to specify the order the rows are returned in using
>>information from another query?
>
>
> This sounds like you are trying to force a join order, not a specific
> tuple order. Have you read the material about join ordering in the
> performance-tips chapter?
>
> regards, tom lane
Honestly no. Though I think I managed to find a quick solution for now
using a temporary table. This cuts down massively on the rows and
makes things pretty quick and slick overall.
I will however look at it, as it might explain why I was having
troubles. If the join could have taken place after rows were cut down,
things would have been more efficient!
Thanks!
Martin Foster
Creator/Designer Ethereal Realms
martin@ethereal-realms.org