pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Josué Maldonado <josue@lamundial.hn>
To: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Join slow on "large" tables
Date: Mon, 07 Jun 2004 17:12:44 -0600
Message-ID: <40C4F66C.8030609@lamundial.hn> (raw)
In-Reply-To: <200406071531.49207.josh@agliodbs.com>
References: <40C4E9EA.7000708@lamundial.hn>
	<200406071531.49207.josh@agliodbs.com>

Hi Josh and thanks for your response,

El 07/06/2004 4:31 PM, Josh Berkus en su mensaje escribio:

> Josue'
> 
> 
>>    ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..11292.52 
>>rows=5831 width=72) (actual time=18.152..39520.406 rows=5049 loops=1)
> 
> 
> Looks to me like there's a problem with index bloat on pkd_pcode_idx.   Try 
> REINDEXing it, and if that doesn't help, VACUUM FULL on pkardex.
> 

Recreated the index (drop then create) and did the vacuum full pkardex 
and the behavior seems to be the same:

dbmund=# explain analyze select * from vkardex where kprocode='1013';
  Nested Loop  (cost=0.00..2248.19 rows=403 width=114) (actual 
time=846.318..16030.633 rows=3145 loops=1)
    ->  Index Scan using pkd_pcode_idx on pkardex  (cost=0.00..806.27 
rows=403 width=72) (actual time=0.054..87.393 rows=3544 loops=1)
          Index Cond: ((pkd_pcode)::text = '1013'::text)
    ->  Index Scan using pdc_pk_idx on pmdoc  (cost=0.00..3.55 rows=1 
width=50) (actual time=4.482..4.484 rows=1 loops=3544)
          Index Cond: (pmdoc.pdc_pk = "outer".doctofk)
  Total runtime: 16033.807 ms
(6 rows)

At the time the querie was running top returned:

5:11pm  up  1:28,  3 users,  load average: 0.19, 0.97, 1.41
69 processes: 66 sleeping, 1 running, 2 zombie, 0 stopped
CPU0 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
CPU1 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
CPU2 states:  0.1% user,  0.4% system,  0.0% nice, 98.4% idle
CPU3 states:  0.0% user,  0.0% system,  0.0% nice, 100.0% idle
Mem:  2069596K av, 1477784K used,  591812K free,       0K shrd,    2336K 
buff
Swap: 2096440K av,    9028K used, 2087412K free                 1388372K 
cached

   PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME COMMAND
  1225 postgres  17   0  257M 257M  255M S     0.6 12.7   7:14 postmaster
  1978 postgres  11   0  1044 1044   860 R     0.2  0.0   0:00 top
     1 root       9   0   472  444   428 S     0.0  0.0   0:04 init
     2 root       8   0     0    0     0 SW    0.0  0.0   0:00 keventd

and free returned:
/root: free
              total       used       free     shared    buffers     cached
Mem:       2069596    1477832     591764          0       2320    1388372
-/+ buffers/cache:      87140    1982456
Swap:      2096440       9028    2087412

I'm not a Linux guru, it looks like a memory leak.


-- 
Sinceramente,

Josué Maldonado.
"Las palabras de aliento después de la censura son como el sol tras el 
aguacero."



view thread (9+ messages)  latest in thread

Message-ID: <40C4F66C.8030609@lamundial.hn>
Permalink:  ../40C4F66C.8030609@lamundial.hn/
Also on:    postgresql.org/message-id/40C4F66C.8030609@lamundial.hn

 · 

reply

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: josue@lamundial.hn, josh@agliodbs.com
  Subject: Re: Join slow on "large" tables
  In-Reply-To: <40C4F66C.8030609@lamundial.hn>

* 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