Today, I looked at 'top' on my PG server and saw a pid that reported 270 hours
of CPU time. Considering this is a very simple query, I was surprised to say
the least. I was about to just kill the pid, but I figured I'd try and see
exactly what it was stuck doing for so long.
Here's the strace summary as run for a few second sample:
% time seconds usecs/call calls errors syscall
------ ----------- ----------- --------- --------- ----------------
97.25 0.671629 92 7272 semop
1.76 0.012171 406 30 recvfrom
0.57 0.003960 66 60 gettimeofday
0.36 0.002512 28 90 sendto
0.05 0.000317 10 32 lseek
0.01 0.000049 1 48 select
------ ----------- ----------- --------- --------- ----------------
100.00 0.690638 7532 total
Here's the query:
select id from eventkeywords where word = '00003322'
If I run the query manually, it completes in about 500ms, which is very reasonable.
There are 408563 rows in this table. I just noticed there is no index on word (
there should be! ). Would this have caused the problem?
This is 8.0.12
Linux sunrise 2.6.15-26-amd64-server #1 SMP Fri Sep 8 20:33:15 UTC 2006 x86_64
GNU/Linux
Any idea what might have set it into this loop?
-Dan