Message-ID: <46A0D836.5090806@drivefaster.net>
Date: Fri, 20 Jul 2007 09:43:50 -0600
From: Dan Harris <fbsd@drivefaster.net>
User-Agent: Thunderbird 2.0.0.4 (Macintosh/20070604)
MIME-Version: 1.0
To: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Simple query showing 270 hours of CPU time
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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