Re: how would you speed up this long query?

On 28/03/15 10:10, zach cruise wrote:
> select
>        sub_query_1.pid,
>        sub_query_1.tit,
>        sub_query_1.num,
>        sub_query_3.cid,
>        sub_query_3.id,
>        sub_query_3.c,
>        sub_query_3.s,
>        sub_query_3.z,
>        sub_query_3.cy,
>        sub_query_3.cd,
>        sub_query_3.cr,
>        org.id as org__id,
>        org.pid as org__pid,
>        org.open,
>        org.cid as org__cid,
>        z0.zcg
> from
>        (select
>                    proj.pid,
>                    proj.tit,
>                    proj.num
>        from
>                    proj,
>                    (select
>                                org.pid
>                    from
>                                org
>                    where
>                                org.open = 'Y') as sub_1
>        where
>                    proj.pid = sub_1.pid) as sub_query_1,
>        (select
>                    detail.cid,
>                    detail.id,
>                    detail.c,
>                    detail.s,
>                    detail.z,
>                    detail.cy,
>                    detail.cd,
>                    detail.cr
>        from
>                    detail,
>                    (select
>                                org.id
>                    from
>                                org
>                    where
>                                org.open = 'Y') as sub_3
>        where
>                    detail.id = sub_3.id) as sub_query_3,
>        org,
>        z0
> where
>        sub_query_1.pid = org.pid and
>        sub_query_3.id = org.id and
>        sub_query_3.z = z0.zcg
> group by
>        z0.zcg,
>        sub_query_1.pid,
>        sub_query_1.tit,
>        sub_query_1.num,
>        sub_query_3.cid,
>        sub_query_3.id,
>        sub_query_3.c,
>        sub_query_3.s,
>        sub_query_3.z,
>        sub_query_3.cy,
>        sub_query_3.cd,
>        sub_query_3.cr,
>        org.id,
>        org.pid,
>        org.open,
>        org.cid
>
>
Version of PostgreSQL?

Operating system?

Hardware configuration?

Indexes?

EXPLAIN ANALYZE output?

Anything else that might be relevant?

What have you already done to investigate?


Cheers,
Gavin