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