Re: help speeding up a query in postgres 8.4.5

Поиск
Список
Период
Сортировка
От Maria L. Wilson
Тема Re: help speeding up a query in postgres 8.4.5
Дата
Msg-id 4D9C6BA6.4070305@nasa.gov
обсуждение исходный текст
Ответ на Re: help speeding up a query in postgres 8.4.5  (Szymon Guz <mabewlun@gmail.com>)
Ответы Re: help speeding up a query in postgres 8.4.5  ("Kevin Grittner" <Kevin.Grittner@wicourts.gov>)
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: tv@fuzzy.cz
Дата:
Сообщение: Re: help speeding up a query in postgres 8.4.5
Следующее
От: "Maria L. Wilson"
Дата:
Сообщение: Re: help speeding up a query in postgres 8.4.5