Re: [PERFORM] help speeding up a query in postgres 8.4.5

[Maria L. Wilson]

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.wilso...@nasa.gov <mailto:maria.l.wilso...@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.wil...@nasa.gov <mailto:m.l.wil...@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