Re: Join slow on "large" tables

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