From: Szymon Guz <mabewlun@gmail.com>
To: Maria.L.Wilson-1@nasa.gov
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: help speeding up a query in postgres 8.4.5
Date: Wed, 6 Apr 2011 13:41:23 +0200
Message-ID: <BANLkTimiH7Gf-PH5g7pTk57+Cjvn-rss3w@mail.gmail.com> (raw)
In-Reply-To: <4D9B6CBA.2050609@nasa.gov>
References: <4D9B6CBA.2050609@nasa.gov>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: mabewlun@gmail.com, Maria.L.Wilson-1@nasa.gov
Subject: Re: help speeding up a query in postgres 8.4.5
In-Reply-To: <BANLkTimiH7Gf-PH5g7pTk57+Cjvn-rss3w@mail.gmail.com>
References: <4D9B6CBA.2050609@nasa.gov> <BANLkTimiH7Gf-PH5g7pTk57+Cjvn-rss3w@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox