Re: help speeding up a query in postgres 8.4.5
От | Szymon Guz |
---|---|
Тема | Re: help speeding up a query in postgres 8.4.5 |
Дата | |
Msg-id | BANLkTimiH7Gf-PH5g7pTk57+Cjvn-rss3w@mail.gmail.com обсуждение исходный текст |
Ответ на | help speeding up a query in postgres 8.4.5 ("Maria L. Wilson" <Maria.L.Wilson-1@nasa.gov>) |
Ответы |
Re: help speeding up a query in postgres 8.4.5
("Maria L. Wilson" <Maria.L.Wilson-1@nasa.gov>)
|
Список | pgsql-performance |
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 по дате отправления: