Re: can I move sort to first outer join ?

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=... ORDER BY c.x
LIMIT N

    You could write :

    SELECT * FROM a LEFT JOIN b LEFT JOIN (SELECT * FROM c WHERE c.column=...
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 reduce
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=0.00..10.90 rows=4
> width=1884) (actual time=0.039..0.109 rows=10 loops=1)
>                            Filter: (visible AND (id = ANY
> ('{3,4,5,6,10,7,8,9,13,11}'::integer[])))
>                      ->  Hash  (cost=10.40..10.40 rows=40 width=1870)
> (actual time=1.048..1.048 rows=101 loops=1)
>                            ->  Seq Scan on features  (cost=0.00..10.40
> rows=40 width=1870) (actual time=0.026..0.464 rows=101 loops=1)
>                ->  Hash  (cost=10.70..10.70 rows=70 width=1065) (actual
> time=0.098..0.098 rows=29 loops=1)
>                      ->  Seq Scan on person_roles  (cost=0.00..10.70
> rows=70 width=1065) (actual time=0.014..0.037 rows=29 loops=1)
>          ->  Hash  (cost=15.80..15.80 rows=580 width=106) (actual
> time=0.105..0.105 rows=32 loops=1)
>                ->  Seq Scan on project_tags  (cost=0.00..15.80 rows=580
> width=106) (actual time=0.013..0.036 rows=32 loops=1)
>  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 scan
(that's the "rows=") 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.