[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] how would you speed up this long query?
From:       zach cruise <zachc1980 () gmail ! com>
Date:       2015-03-31 18:56:04
Message-ID: CAL8icXym6HYfiS=GtKNWn88MD7UR6JjYJ9BaODiTfeN8=5QH8w () mail ! gmail ! com
[Download RAW message or body]

> 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  (costU20.89..6335.03 rows092 width9) (actual
time864.186..4402.447 rowsU12 loops=1)"
 "  ->  Sort  (costU20.89..5566.12 rows092 width9) (actual
time864.171..4146.725 rows—141 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  (cost541.48..4241.51 rows092 width9)
(actual time%4.216..432.629 rows—141 loops=1)"
 "              Hash Cond: (org.id = detail.id)"
 "              ->  Hash Join  (costu2.72..1036.45 rowsI55
width9) (actual timed.492..86.822 rowsI77 loops=1)"
 "                    Hash Cond: (org.pid = proj.pid)"
 "                    ->  Seq Scan on org  (cost=0.00..196.82
rowsI82 width&) (actual time=0.024..6.199 rowsI82 loops=1)"
 "                    ->  Hash  (costp2.97..702.97 rows980
width‘) (actual timed.439..64.439 rows973 loops=1)"
 "                          Buckets: 1024  Batches: 1  Memory Usage: 465kB"
 "                          ->  Hash Join  (costB4.04..702.97
rows980 width‘) (actual time .994..52.773 rows973 loops=1)"
 "                                Hash Cond: (org_1.pid = proj.pid)"
 "                                ->  Seq Scan on org org_1
(cost=0.00..209.28 rows980 width=8) (actual time=0.016..10.815
rows980 loops=1)"
 "                                      Filter: ((open)::text = 'Y'::text)"
 "                                      Rows Removed by Filter: 1002"
 "                                ->  Hash  (cost74.02..374.02
rows@02 widthƒ) (actual time .950..20.950 rows@02 loops=1)"
 "                                      Buckets: 1024  Batches: 1
Memory Usage: 424kB"
 "                                      ->  Seq Scan on proj
(cost=0.00..374.02 rows@02 widthƒ) (actual time=0.010..9.810
rows@02 loops=1)"
 "              ->  Hash  (cost'16.44..2716.44 rowsW86 width˜)
(actual time9.677..189.677 rowsI59 loops=1)"
 "                    Buckets: 1024  Batches: 1  Memory Usage: 629kB"
 "                    ->  Hash Join  (cost#69.71..2716.44 rowsW86
width˜) (actual time9.635..182.956 rowsI59 loops=1)"
 "                          Hash Cond: (org_2.id = detail.id)"
 "                          ->  Seq Scan on org org_2
(cost=0.00..209.28 rows980 width=8) (actual time=0.015..4.194
rows980 loops=1)"
 "                                Filter: ((open)::text = 'Y'::text)"
 "                                Rows Removed by Filter: 1002"
 "                          ->  Hash  (cost#40.92..2340.92 rows#03
width) (actual time9.596..169.596 rows64 loops=1)"
 "                                Buckets: 1024  Batches: 1  Memory
Usage: 224kB"
 "                                ->  Hash Join
(cost 69.93..2340.92 rows#03 width) (actual
time9.126..166.937 rows64 loops=1)"
 "                                      Hash Cond: ((detail.z)::text (z0.zcg)::text)"
 "                                      ->  Seq Scan on detail
(cost=0.00..199.03 rows#03 widthR) (actual time=0.009..2.152
rows#03 loops=1)"
 "                                      ->  Hash
(cost38.30..1538.30 rowsB530 width8) (actual
time9.070..159.070 rowsB530 loops=1)"
 "                                            Buckets: 8192  Batches:
1  Memory Usage: 2451kB"
 "                                            ->  Seq Scan on z0
(cost=0.00..1538.30 rowsB530 width8) (actual time=0.010..82.125
rowsB530 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
[prev in list] [next in list] [prev in thread] [next in thread]