Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 29875133655C for ; Tue, 5 Apr 2011 16:26:06 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 57304-03 for ; Tue, 5 Apr 2011 19:25:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ndmsnpf03.ndc.nasa.gov (ndmsnpf03.ndc.nasa.gov [198.117.0.123]) by mail.postgresql.org (Postfix) with ESMTP id 3429A13364DE for ; Tue, 5 Apr 2011 16:25:48 -0300 (ADT) Received: from ndmsppt04.ndc.nasa.gov (ndmsppt04.ndc.nasa.gov [198.117.0.103]) by ndmsnpf03.ndc.nasa.gov (Postfix) with ESMTP id 9070E182E3A for ; Tue, 5 Apr 2011 14:25:47 -0500 (CDT) Received: from ndmshub01.ndc.nasa.gov (ndmshub01-pub.ndc.nasa.gov [198.117.0.160]) by ndmsppt04.ndc.nasa.gov (8.14.4/8.14.4) with ESMTP id p35JPlnW019319 for ; Tue, 5 Apr 2011 14:25:47 -0500 Received: from lrc000959195.larc.nasa.gov (198.119.134.170) by smtp02.ndc.nasa.gov (198.117.0.160) with Microsoft SMTP Server (TLS) id 8.3.137.0; Tue, 5 Apr 2011 14:25:47 -0500 Message-ID: <4D9B6CBA.2050609@nasa.gov> Date: Tue, 5 Apr 2011 15:25:46 -0400 From: "Maria L. Wilson" Reply-To: Maria.L.Wilson-1@nasa.gov Organization: SSAI User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.6; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9 MIME-Version: 1.0 To: "pgsql-performance@postgresql.org" Subject: help speeding up a query in postgres 8.4.5 Content-Type: multipart/alternative; boundary="------------020300080408000405010602" X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:5.2.15, 1.0.148, 0.0.0000 definitions=2011-04-05_08:2011-04-05, 2011-04-05, 1970-01-01 signatures=0 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201104/43 X-Sequence-Number: 43105 --------------020300080408000405010602 Content-Type: text/plain; charset="ISO-8859-1"; format=flowed Content-Transfer-Encoding: 7bit Would really appreciate someone taking a look at the query below.... Thanks in advance! this is on a linux box... Linux dsrvr201.larc.nasa.gov 2.6.18-164.9.1.el5 #1 SMP Wed Dec 9 03:27:37 EST 2009 x86_64 x86_64 x86_64 GNU/Linux explain analyze select MIN(IV.STRTDATE), MAX(IV.ENDDATE) from GRAN_VER GV left outer join INVENTORY IV on GV.GRANULE_ID = IV.GRANULE_ID, INVSENSOR INVS where IV.INV_ID='65' and GV.GRANULE_ID = INVS.granule_id and INVS.sensor_id='13' "Aggregate (cost=736364.52..736364.53 rows=1 width=8) (actual time=17532.930..17532.930 rows=1 loops=1)" " -> Hash Join (cost=690287.33..734679.77 rows=336949 width=8) (actual time=13791.593..17323.080 rows=924675 loops=1)" " Hash Cond: (invs.granule_id = gv.granule_id)" " -> Seq Scan on invsensor invs (cost=0.00..36189.41 rows=1288943 width=4) (actual time=0.297..735.375 rows=1277121 loops=1)" " Filter: (sensor_id = 13)" " -> Hash (cost=674401.52..674401.52 rows=1270865 width=16) (actual time=13787.698..13787.698 rows=1270750 loops=1)" " -> Hash Join (cost=513545.62..674401.52 rows=1270865 width=16) (actual time=1998.702..13105.578 rows=1270750 loops=1)" " Hash Cond: (gv.granule_id = iv.granule_id)" " -> Seq Scan on gran_ver gv (cost=0.00..75224.90 rows=4861490 width=4) (actual time=0.008..1034.885 rows=4867542 loops=1)" " -> Hash (cost=497659.81..497659.81 rows=1270865 width=12) (actual time=1968.918..1968.918 rows=1270750 loops=1)" " -> Bitmap Heap Scan on inventory iv (cost=24050.00..497659.81 rows=1270865 width=12) (actual time=253.542..1387.957 rows=1270750 loops=1)" " Recheck Cond: (inv_id = 65)" " -> Bitmap Index Scan on inven_idx1 (cost=0.00..23732.28 rows=1270865 width=0) (actual time=214.364..214.364 rows=1270977 loops=1)" " Index Cond: (inv_id = 65)" "Total runtime: 17533.100 ms" some additional info..... the table inventory is about 4481 MB and also has postgis types. the table gran_ver is about 523 MB the table INVSENSOR is about 217 MB the server itself has 32G RAM with the following set in the postgres conf shared_buffers = 3GB work_mem = 64MB maintenance_work_mem = 512MB wal_buffers = 6MB let me know if I've forgotten anything! thanks a bunch!! Maria Wilson NASA/Langley Research Center Hampton, Virginia m.l.wilson@nasa.gov * * --------------020300080408000405010602 Content-Type: text/html; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit Would really appreciate someone taking a look at the query below....  Thanks in advance!


this is on a linux box...
Linux dsrvr201.larc.nasa.gov 2.6.18-164.9.1.el5 #1 SMP Wed Dec 9 03:27:37 EST 2009 x86_64 x86_64 x86_64 GNU/Linux

explain analyze
select MIN(IV.STRTDATE), MAX(IV.ENDDATE)
from GRAN_VER GV
left outer join INVENTORY IV on GV.GRANULE_ID = IV.GRANULE_ID, INVSENSOR INVS
where IV.INV_ID='65' and GV.GRANULE_ID = INVS.granule_id and INVS.sensor_id='13'


"Aggregate  (cost=736364.52..736364.53 rows=1 width=8) (actual time=17532.930..17532.930 rows=1 loops=1)"
"  ->  Hash Join  (cost=690287.33..734679.77 rows=336949 width=8) (actual time=13791.593..17323.080 rows=924675 loops=1)"
"        Hash Cond: (invs.granule_id = gv.granule_id)"
"        ->  Seq Scan on invsensor invs  (cost=0.00..36189.41 rows=1288943 width=4) (actual time=0.297..735.375 rows=1277121 loops=1)"
"              Filter: (sensor_id = 13)"
"        ->  Hash  (cost=674401.52..674401.52 rows=1270865 width=16) (actual time=13787.698..13787.698 rows=1270750 loops=1)"
"              ->  Hash Join  (cost=513545.62..674401.52 rows=1270865 width=16) (actual time=1998.702..13105.578 rows=1270750 loops=1)"
"                    Hash Cond: (gv.granule_id = iv.granule_id)"
"                    ->  Seq Scan on gran_ver gv  (cost=0.00..75224.90 rows=4861490 width=4) (actual time=0.008..1034.885 rows=4867542 loops=1)"
"                    ->  Hash  (cost=497659.81..497659.81 rows=1270865 width=12) (actual time=1968.918..1968.918 rows=1270750 loops=1)"
"                          ->  Bitmap Heap Scan on inventory iv  (cost=24050.00..497659.81 rows=1270865 width=12) (actual time=253.542..1387.957 rows=1270750 loops=1)"
"                                Recheck Cond: (inv_id = 65)"
"                                ->  Bitmap Index Scan on inven_idx1  (cost=0.00..23732.28 rows=1270865 width=0) (actual time=214.364..214.364 rows=1270977 loops=1)"
"                                      Index Cond: (inv_id = 65)"
"Total runtime: 17533.100 ms"

some additional info.....
the table inventory is about 4481 MB and also has postgis types.
the table gran_ver is about 523 MB
the table INVSENSOR is about 217 MB

the server itself has 32G RAM with the following set in the postgres conf
shared_buffers = 3GB
work_mem = 64MB                      
maintenance_work_mem = 512MB       
wal_buffers = 6MB

let me know if I've forgotten anything!  thanks a bunch!!

Maria Wilson
NASA/Langley Research Center
Hampton, Virginia
m.l.wilson@nasa.gov




 --------------020300080408000405010602--