From: Martin Nickel <martin@portant.com>
Subject: How can this be?
Date: Fri, 16 Sep 2005 20:34:14 -0500
Organization: Hub.Org Networking Services
Lines: 51
Message-ID: <pan.2005.09.17.01.34.11.467862@portant.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
To: pgsql.performance@postgresql.org
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing
	moment of clarity.)
To: pgsql-performance@postgresql.org

Hello all,
Mostly Postgres makes sense to me.  But now and then it does something
that  boggles my brain.  Take the statements below.  I have a table
(agent) with 5300 rows.  The primary key is agent_id.  I can do SELECT
agent_id FROM agent and it returns all PK values in less than half a
second (dual Opteron box, 4G ram, SATA Raid 10 drive system).

But when I do a DELETE on two rows with an IN statement, using the primary
key index (as stated by EXPLAIN) it take almost 4 minutes.
pg_stat_activity shows nine other connections, all idle.

If someone can explain this to me it will help restore my general faith in
order and consistancy in the universe.

Martin


-- Executing query:
SELECT count(*) from agent;
Total query runtime: 54 ms.
Data retrieval runtime: 31 ms.
1 rows retrieved.
Result: 5353

-- Executing query:
VACUUM ANALYZE agent;

-- Executing query:
DELETE FROM agent WHERE agent_id IN (15395, 15394);
Query returned successfully: 2 rows affected, 224092 ms execution time.

-- Executing query:
EXPLAIN DELETE FROM agent WHERE agent_id IN (15395, 15394);
Index Scan using agent2_pkey, agent2_pkey on agent  (cost=0.00..7.27
rows=2 width=6)
Index Cond: ((agent_id = 15395) OR (agent_id = 15394))

Here's my table
CREATE TABLE agent
(
  agent_id int4 NOT NULL DEFAULT nextval('agent_id_seq'::text),
  office_id int4 NOT NULL,
  lastname varchar(25),
  firstname varchar(25),
...other columns...  
  CONSTRAINT agent2_pkey PRIMARY KEY (agent_id),
  CONSTRAINT agent_office_fk FOREIGN KEY (office_id) REFERENCES office (office_id) ON UPDATE RESTRICT ON DELETE RESTRICT
) 
WITHOUT OIDS;