Simple query showing 270 hours of CPU time

From: Dan Harris <fbsd(at)drivefaster(dot)net>
To: PostgreSQL Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Simple query showing 270 hours of CPU time
Date: 2007-07-20 15:43:50
Message-ID: 46A0D836.5090806@drivefaster.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tom Lane 2007-07-20 16:42:05 Re: Simple query showing 270 hours of CPU time
Previous Message Gregory Stark 2007-07-20 11:46:07 Re: User concurrency thresholding: where do I look?