Re: help speeding up a query in postgres 8.4.5

["Maria L. Wilson" <Maria.L.Wilson-1@xxxxxxxx>]

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@xxxxxxxx> 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@xxxxxxxx

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