Re: help speeding up a query in postgres 8.4.5 - Mailing list pgsql-performance
From | Maria L. Wilson |
---|---|
Subject | Re: help speeding up a query in postgres 8.4.5 |
Date | |
Msg-id | 4DC97A40.7010905@nasa.gov Whole thread Raw |
In response to | Re: help speeding up a query in postgres 8.4.5 (Robert Haas <robertmhaas@gmail.com>) |
List | pgsql-performance |
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)? >
pgsql-performance by date: