Re: Join slow on "large" tables - Mailing list pgsql-performance

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."