From: Maria L. Wilson <Maria.L.Wilson-1@nasa.gov>
To: Szymon Guz <mabewlun@gmail.com>
Cc: Wilson, Maria Louise (LARC-E301)[SCIENCE SYSTEMS AND APPLICATIONS, INC] <m.l.wilson@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 09:33:26 -0400
Message-ID: <4D9C6BA6.4070305@nasa.gov> (raw)
In-Reply-To: <BANLkTimiH7Gf-PH5g7pTk57+Cjvn-rss3w@mail.gmail.com>
References: <4D9B6CBA.2050609@nasa.gov>
<BANLkTimiH7Gf-PH5g7pTk57+Cjvn-rss3w@mail.gmail.com>
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
> <mailto: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 <http://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 <mailto: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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: Maria.L.Wilson-1@nasa.gov, mabewlun@gmail.com, m.l.wilson@nasa.gov
Subject: Re: help speeding up a query in postgres 8.4.5
In-Reply-To: <4D9C6BA6.4070305@nasa.gov>
* 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