Date: Wed, 14 May 2008 11:58:09 +0200
To: Fernando.Castano@sun.com, pgsql-performance@postgresql.org
Subject: Re: can I move sort to first outer join ?
From: PFC <lists@peufeu.com>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <684043.20585.qm@web35307.mail.mud.yahoo.com>
	<482A6D48.9080509@sun.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.ua41y7z7cigqcu@apollo13.peufeu.com>
In-Reply-To: <482A6D48.9080509@sun.com>
User-Agent: Opera Mail/9.24 (Linux)

On Wed, 14 May 2008 06:40:40 +0200, fernando castano  =

<Fernando.Castano@Sun.COM> wrote:

>
> Hi all,
>
> This sql is taking too long for the size of my tiny db.  Any tips from=
  =

> this alias?  I tried moving the sort to the first left outer join
> (between projects and features tables) using a nested subquery, but  =

> postgres tells me only one column could be returned from a subqueyr.

	Instead of :

	SELECT * FROM a LEFT JOIN b LEFT JOIN c WHERE c.column=3D... ORDER BY c=
.x  =

LIMIT N

	You could write :

	SELECT * FROM a LEFT JOIN b LEFT JOIN (SELECT * FROM c WHERE c.column=3D=
...  =

ORDER BY c.x LIMIT N) AS cc ORDER BY cc.x LIMIT N

	This is only interesting of you use a LIMIT and this allows you to redu=
ce  =

the number of rows sorted/joined.

	However in your case this is not the right thing to do since you do not=
  =

use LIMIT, and sorting your 846 rows will only take a very small time.  =

Your problem are those seq scans, you need to optimize that query so it =
 =

can use indexes.

>                      ->  Seq Scan on projects  (cost=3D0.00..10.90 row=
s=3D4  =

> width=3D1884) (actual time=3D0.039..0.109 rows=3D10 loops=3D1)
>                            Filter: (visible AND (id =3D ANY  =

> ('{3,4,5,6,10,7,8,9,13,11}'::integer[])))
>                      ->  Hash  (cost=3D10.40..10.40 rows=3D40 width=3D=
1870)  =

> (actual time=3D1.048..1.048 rows=3D101 loops=3D1)
>                            ->  Seq Scan on features  (cost=3D0.00..10.=
40  =

> rows=3D40 width=3D1870) (actual time=3D0.026..0.464 rows=3D101 loops=3D=
1)
>                ->  Hash  (cost=3D10.70..10.70 rows=3D70 width=3D1065) =
(actual  =

> time=3D0.098..0.098 rows=3D29 loops=3D1)
>                      ->  Seq Scan on person_roles  (cost=3D0.00..10.70=
  =

> rows=3D70 width=3D1065) (actual time=3D0.014..0.037 rows=3D29 loops=3D=
1)
>          ->  Hash  (cost=3D15.80..15.80 rows=3D580 width=3D106) (actua=
l  =

> time=3D0.105..0.105 rows=3D32 loops=3D1)
>                ->  Seq Scan on project_tags  (cost=3D0.00..15.80 rows=3D=
580  =

> width=3D106) (actual time=3D0.013..0.036 rows=3D32 loops=3D1)
>  Total runtime: 149.622 ms

	All those seq scans !!!

	Please post, for each of those tables :

	- The total number of rows (SELECT count(*) is fine)
	- The table definitions with indexes (\d table)

	EXPLAIN ANALYZE tells you the number of rows it picked out of a seq sca=
n  =

(that's the "rows=3D") but not the number of rows scanned... this is  =

important, because a seq scan on a small table isn't a problem, but on a=
  =

big one, it is.