Message-ID: <4DC97A40.7010905@nasa.gov>
Date: Tue, 10 May 2011 13:47:44 -0400
From: "Maria L. Wilson" <Maria.L.Wilson-1@nasa.gov>
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.17) Gecko/20110414 Lightning/1.0b2 Thunderbird/3.1.10
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>
CC: "Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS AND APPLICATIONS, INC]"
	<m.l.wilson@nasa.gov>, "pgsql-performance@postgresql.org"
	<pgsql-performance@postgresql.org>
Subject: Re: help speeding up a query in postgres 8.4.5
References: <4D9B6CBA.2050609@nasa.gov>
	<BANLkTim+36AxCNq+KPCFwaE9fGKTn-8S5Q@mail.gmail.com>
In-Reply-To: <BANLkTim+36AxCNq+KPCFwaE9fGKTn-8S5Q@mail.gmail.com>
Content-Type: text/plain; charset="ISO-8859-1"; format=flowed
Content-Transfer-Encoding: 7bit

thanks for taking a look at this....  and it's never too late!!

I've tried bumping up work_mem and did not see any improvements -
All the indexes do exist that you asked.... see below....
Any other ideas?

CREATE INDEX invsnsr_idx1
   ON invsensor
   USING btree
   (granule_id);

CREATE INDEX invsnsr_idx2
   ON invsensor
   USING btree
   (sensor_id);

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

thanks for the look -
Maria Wilson
NASA/Langley Research Center
Hampton, Virginia 23681
m.l.wilson@nasa.gov

On 5/10/11 1:38 PM, Robert Haas wrote:
> On Tue, Apr 5, 2011 at 3:25 PM, 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!!
> Late response here, but...
>
> Is there an index on invsensor (sensor_id, granule_id)?  If not, that
> might be something to try.  If so, you might want to try to figure out
> why it's not being used.
>
> Likewise, is there an index on gran_ver (granule_id)?
>