Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id EE2BB1337C54 for ; Wed, 6 Apr 2011 10:33:57 -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 61643-09 for ; Wed, 6 Apr 2011 13:33:38 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from ndjsnpf03.ndc.nasa.gov (ndjsnpf03.ndc.nasa.gov [198.117.1.123]) by mail.postgresql.org (Postfix) with ESMTP id A3F131337C0A for ; Wed, 6 Apr 2011 10:33:27 -0300 (ADT) Received: from ndmsppt05.ndc.nasa.gov (ndmsppt05.ndc.nasa.gov [198.117.0.104]) by ndjsnpf03.ndc.nasa.gov (Postfix) with ESMTP id 75EEC2D8AFD; Wed, 6 Apr 2011 08:33:27 -0500 (CDT) Received: from ndmshub01.ndc.nasa.gov (ndmshub01-pub.ndc.nasa.gov [198.117.0.160]) by ndmsppt05.ndc.nasa.gov (8.14.4/8.14.4) with ESMTP id p36DXRKW009376; Wed, 6 Apr 2011 08:33:27 -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; Wed, 6 Apr 2011 08:33:26 -0500 Message-ID: <4D9C6BA6.4070305@nasa.gov> Date: Wed, 6 Apr 2011 09:33:26 -0400 From: "Maria L. Wilson" Reply-To: 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: Szymon Guz CC: "Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS AND APPLICATIONS, INC]" , "pgsql-performance@postgresql.org" Subject: Re: help speeding up a query in postgres 8.4.5 References: <4D9B6CBA.2050609@nasa.gov> In-Reply-To: Content-Type: multipart/alternative; boundary="------------010705000605080005000201" X-Proofpoint-Virus-Version: vendor=fsecure engine=2.50.10432:5.2.15, 1.0.148, 0.0.0000 definitions=2011-04-06_04:2011-04-06, 2011-04-06, 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/46 X-Sequence-Number: 43108 --------------010705000605080005000201 Content-Type: text/plain; charset="UTF-8"; format=flowed Content-Transfer-Encoding: 7bit Autovacuum is not running - but regular vacuums are being done twice daily. indexes on inventory: CREATE INDEX inven_idx1 ON inventory USING btree (inv_id); CREATE UNIQUE INDEX inven_idx2 ON inventory USING btree (granule_id); indexes on gran_ver: CREATE UNIQUE INDEX granver_idx1 ON gran_ver USING btree (granule_id); indexes on sensor CREATE INDEX invsnsr_idx2 ON invsensor USING btree (sensor_id); On 4/6/11 7:41 AM, Szymon Guz wrote: > > > On 5 April 2011 21:25, Maria L. Wilson > wrote: > > 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 > > > > Hi, > could you show us indexes that you have on all tables from this query? > Have you tried running vacuum analyze on those tables? Do you have > autovacuum active? > > regards > Szymon --------------010705000605080005000201 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: 8bit Autovacuum is not running - but regular vacuums are being done twice daily.

indexes on inventory:

CREATE INDEX inven_idx1
  ON inventory
  USING btree
  (inv_id);

CREATE UNIQUE INDEX inven_idx2
  ON inventory
  USING btree
  (granule_id);

indexes on gran_ver:
CREATE UNIQUE INDEX granver_idx1
  ON gran_ver
  USING btree
  (granule_id);

indexes on sensor
CREATE INDEX invsnsr_idx2
  ON invsensor
  USING btree
  (sensor_id);




On 4/6/11 7:41 AM, Szymon Guz wrote:


On 5 April 2011 21:25, Maria L. Wilson <Maria.L.Wilson-1@nasa.gov> wrote:
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



Hi,
could you show us indexes that you have on all tables from this query? Have you tried running vacuum analyze on those tables? Do you have autovacuum active?

regards
Szymon
--------------010705000605080005000201--