Postgres Pro
Downloads
Home   >   mailing lists

Thread: subselect, order by and left join

subselect, order by and left join

From
"Morten K. Poulsen"
Date:
(re-post)

Dear list,

Please let me know if this is not the list to ask this kind of question.

I am trying to optimize a query that joins two relatively large (750000 rows in
each) tables. If I do it using a subselect, I can "force" the planner to choose
the fastest path. Now, my question is:

If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the
other table, is the order maintained? Or is PostgreSQL free to return the rows
in any order, after the join?

My query is the following:

SELECT a.*
FROM (SELECT * FROM tree WHERE parent_id=1363405 ORDER BY order_index DESC) AS a
LEFT JOIN content AS b ON a.object_id=b.id
WHERE (b.onair = 't') LIMIT 1;

Thanks,
Morten

--
Morten K. Poulsen <morten-postgresql@afdelingp.dk>
http://www.afdelingp.dk/

Re: subselect, order by and left join

From
Stephan Szabo
Date:
On Mon, 8 Nov 2004, Morten K. Poulsen wrote:

> Please let me know if this is not the list to ask this kind of question.
>
> I am trying to optimize a query that joins two relatively large (750000 rows in
> each) tables. If I do it using a subselect, I can "force" the planner to choose
> the fastest path. Now, my question is:
>
> If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the
> other table, is the order maintained? Or is PostgreSQL free to return the rows
> in any order, after the join?

AFAIK, you have no guarantees as to the output order unless you have
another order by.  The join may destroy the ordering, so even if you get
the ordering you want right now, you shouldn't rely on it.

Re: subselect, order by and left join

From
"Morten K. Poulsen"
Date:
On Mon, Nov 08, 2004 at 04:54:40AM -0800, Stephan Szabo wrote:
> > If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the
> > other table, is the order maintained? Or is PostgreSQL free to return the
> > rows in any order, after the join?
>
> AFAIK, you have no guarantees as to the output order unless you have another
> order by.  The join may destroy the ordering, so even if you get the ordering
> you want right now, you shouldn't rely on it.

OK. Thanks for the reply.

Morten

--
Morten K. Poulsen <morten-postgresql@afdelingp.dk>
http://www.afdelingp.dk/

Re: subselect, order by and left join

From
Karim Nassar
Date:
On Mon, 2004-11-08 at 05:54, Stephan Szabo wrote:
> > If I have a subselect with an ORDER BY, and I LEFT JOIN the result with the
> > other table, is the order maintained? Or is PostgreSQL free to return the rows
> > in any order, after the join?
>
> AFAIK, you have no guarantees as to the output order unless you have
> another order by.  The join may destroy the ordering, so even if you get
> the ordering you want right now, you shouldn't rely on it.

Try http://www.varlena.com/varlena/GeneralBits/35.html

If you look under "Alphanumeric Sorting" about halfway down the page,
you will find the path toward the magic you are looking for.

A particularly nasty working example:

SELECT sort_order, col, code, description, units,
       TO_CHAR(min_value, 'FM99999999999D90') AS min_value,
       TO_CHAR(max_value, 'FM99999999999D90') AS max_value,
       value AS dv_text, id_datatype_value
 FROM ( SELECT *, (CASE WHEN (SUBSTRING(dv.value FROM '^[0-9\.]{1,3}') IS NOT NULL)
                        THEN (SUBSTRING(dv.value FROM '^[0-9\.]{1,3}')::numeric)
                        ELSE NULL
                    END) AS sort_order
         FROM datasheet ds JOIN datasheet_column dc  USING (id_datasheet)
                           JOIN datatype dt          USING (id_datatype)
                      LEFT JOIN datatype_value dv    USING (id_datatype)
        WHERE id_datasheet = '7') END_SORT_FU
ORDER BY col, sort_order, value

\<.