how would you speed up this long query?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Version of PostgreSQL?
9.3
Operating system?
win
Hardware configuration?
8 gb ram. takes about 7000 ms to retrieve about 7000 rows.
max_connections = 200
shared_buffers = 512mb
effective_cache_size = 6gb
work_mem = 13107kb
maintenance_work_mem = 512mb
checkpoint_segments = 32
checkpoint_completion_target = 0.7
wal_buffers = 16mb
default_statistics_target = 100
Indexes?
no
Anything else that might be relevant?
no
What have you already done to investigate?
moved subquery from "where" to "from" to evaluate once instead of once per row
EXPLAIN ANALYZE output?
would help if you can help us understand what's going on here:
"Group (cost=5520.89..6335.03 rows=18092 width=199) (actual
time=3864.186..4402.447 rows=5512 loops=1)"
" -> Sort (cost=5520.89..5566.12 rows=18092 width=199) (actual
time=3864.171..4146.725 rows=97141 loops=1)"
" Sort Key: z0.zcg, proj.pid, proj.tit, proj.num, detail.cid,
detail.id, det (...)"
" Sort Method: external merge Disk: 21648kB"
" -> Hash Join (cost=3541.48..4241.51 rows=18092 width=199)
(actual time=254.216..432.629 rows=97141 loops=1)"
" Hash Cond: (org.id = detail.id)"
" -> Hash Join (cost=752.72..1036.45 rows=4955
width=109) (actual time=64.492..86.822 rows=4977 loops=1)"
" Hash Cond: (org.pid = proj.pid)"
" -> Seq Scan on org (cost=0.00..196.82
rows=4982 width=26) (actual time=0.024..6.199 rows=4982 loops=1)"
" -> Hash (cost=702.97..702.97 rows=3980
width=91) (actual time=64.439..64.439 rows=3973 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 465kB"
" -> Hash Join (cost=424.04..702.97
rows=3980 width=91) (actual time=20.994..52.773 rows=3973 loops=1)"
" Hash Cond: (org_1.pid = proj.pid)"
" -> Seq Scan on org org_1
(cost=0.00..209.28 rows=3980 width=8) (actual time=0.016..10.815
rows=3980 loops=1)"
" Filter: ((open)::text = 'Y'::text)"
" Rows Removed by Filter: 1002"
" -> Hash (cost=374.02..374.02
rows=4002 width=83) (actual time=20.950..20.950 rows=4002 loops=1)"
" Buckets: 1024 Batches: 1
Memory Usage: 424kB"
" -> Seq Scan on proj
(cost=0.00..374.02 rows=4002 width=83) (actual time=0.010..9.810
rows=4002 loops=1)"
" -> Hash (cost=2716.44..2716.44 rows=5786 width=98)
(actual time=189.677..189.677 rows=4959 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 629kB"
" -> Hash Join (cost=2369.71..2716.44 rows=5786
width=98) (actual time=169.635..182.956 rows=4959 loops=1)"
" Hash Cond: (org_2.id = detail.id)"
" -> Seq Scan on org org_2
(cost=0.00..209.28 rows=3980 width=8) (actual time=0.015..4.194
rows=3980 loops=1)"
" Filter: ((open)::text = 'Y'::text)"
" Rows Removed by Filter: 1002"
" -> Hash (cost=2340.92..2340.92 rows=2303
width=90) (actual time=169.596..169.596 rows=1964 loops=1)"
" Buckets: 1024 Batches: 1 Memory
Usage: 224kB"
" -> Hash Join
(cost=2069.93..2340.92 rows=2303 width=90) (actual
time=159.126..166.937 rows=1964 loops=1)"
" Hash Cond: ((detail.z)::text =
(z0.zcg)::text)"
" -> Seq Scan on detail
(cost=0.00..199.03 rows=2303 width=52) (actual time=0.009..2.152
rows=2303 loops=1)"
" -> Hash
(cost=1538.30..1538.30 rows=42530 width=38) (actual
time=159.070..159.070 rows=42530 loops=1)"
" Buckets: 8192 Batches:
1 Memory Usage: 2451kB"
" -> Seq Scan on z0
(cost=0.00..1538.30 rows=42530 width=38) (actual time=0.010..82.125
rows=42530 loops=1)"
"Total runtime: 4414.655 ms"
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of zach cruise
Sent: Tuesday, March 31, 2015 2:56 PM
To: Gavin Flower
Cc: PostgreSQL
Subject: Re: [GENERAL] how would you speed up this long query?
Version of PostgreSQL?
9.3
Operating system?
win
Hardware configuration?
8 gb ram. takes about 7000 ms to retrieve about 7000 rows.
max_connections = 200
shared_buffers = 512mb
effective_cache_size = 6gb
work_mem = 13107kb
maintenance_work_mem = 512mb
checkpoint_segments = 32
checkpoint_completion_target = 0.7
wal_buffers = 16mb
default_statistics_target = 100
Indexes?
no
Anything else that might be relevant?
no
What have you already done to investigate?
moved subquery from "where" to "from" to evaluate once instead of once per row
EXPLAIN ANALYZE output?
would help if you can help us understand what's going on here:
"Group (cost=5520.89..6335.03 rows=18092 width=199) (actual
time=3864.186..4402.447 rows=5512 loops=1)"
" -> Sort (cost=5520.89..5566.12 rows=18092 width=199) (actual
time=3864.171..4146.725 rows=97141 loops=1)"
" Sort Key: z0.zcg, proj.pid, proj.tit, proj.num, detail.cid,
detail.id, det (...)"
" Sort Method: external merge Disk: 21648kB"
" -> Hash Join (cost=3541.48..4241.51 rows=18092 width=199)
(actual time=254.216..432.629 rows=97141 loops=1)"
" Hash Cond: (org.id = detail.id)"
" -> Hash Join (cost=752.72..1036.45 rows=4955
width=109) (actual time=64.492..86.822 rows=4977 loops=1)"
" Hash Cond: (org.pid = proj.pid)"
" -> Seq Scan on org (cost=0.00..196.82
rows=4982 width=26) (actual time=0.024..6.199 rows=4982 loops=1)"
" -> Hash (cost=702.97..702.97 rows=3980
width=91) (actual time=64.439..64.439 rows=3973 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 465kB"
" -> Hash Join (cost=424.04..702.97
rows=3980 width=91) (actual time=20.994..52.773 rows=3973 loops=1)"
" Hash Cond: (org_1.pid = proj.pid)"
" -> Seq Scan on org org_1
(cost=0.00..209.28 rows=3980 width=8) (actual time=0.016..10.815
rows=3980 loops=1)"
" Filter: ((open)::text = 'Y'::text)"
" Rows Removed by Filter: 1002"
" -> Hash (cost=374.02..374.02
rows=4002 width=83) (actual time=20.950..20.950 rows=4002 loops=1)"
" Buckets: 1024 Batches: 1
Memory Usage: 424kB"
" -> Seq Scan on proj
(cost=0.00..374.02 rows=4002 width=83) (actual time=0.010..9.810
rows=4002 loops=1)"
" -> Hash (cost=2716.44..2716.44 rows=5786 width=98)
(actual time=189.677..189.677 rows=4959 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 629kB"
" -> Hash Join (cost=2369.71..2716.44 rows=5786
width=98) (actual time=169.635..182.956 rows=4959 loops=1)"
" Hash Cond: (org_2.id = detail.id)"
" -> Seq Scan on org org_2
(cost=0.00..209.28 rows=3980 width=8) (actual time=0.015..4.194
rows=3980 loops=1)"
" Filter: ((open)::text = 'Y'::text)"
" Rows Removed by Filter: 1002"
" -> Hash (cost=2340.92..2340.92 rows=2303
width=90) (actual time=169.596..169.596 rows=1964 loops=1)"
" Buckets: 1024 Batches: 1 Memory
Usage: 224kB"
" -> Hash Join
(cost=2069.93..2340.92 rows=2303 width=90) (actual
time=159.126..166.937 rows=1964 loops=1)"
" Hash Cond: ((detail.z)::text =
(z0.zcg)::text)"
" -> Seq Scan on detail
(cost=0.00..199.03 rows=2303 width=52) (actual time=0.009..2.152
rows=2303 loops=1)"
" -> Hash
(cost=1538.30..1538.30 rows=42530 width=38) (actual
time=159.070..159.070 rows=42530 loops=1)"
" Buckets: 8192 Batches:
1 Memory Usage: 2451kB"
" -> Seq Scan on z0
(cost=0.00..1538.30 rows=42530 width=38) (actual time=0.010..82.125
rows=42530 loops=1)"
"Total runtime: 4414.655 ms"
--
Didn't see replies to this message, so...
Your query spends most of the time on sorting:
" Sort Method: external merge Disk: 21648kB"
and it doesn't fit in memory.
Try increasing work_mem somewhat to 50MB, you could do it for this particular connection only, if you don't want to change it for the whole server.
Regards,
Igor Neyman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general