From: | Martin Nickel <martin(at)portant(dot)com> |
---|---|
To: | pgsql(dot)performance(at)postgresql(dot)org |
Subject: | How can this be? |
Date: | 2005-09-17 01:34:14 |
Message-ID: | pan.2005.09.17.01.34.11.467862@portant.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Antoine Bajolet | 2005-09-17 15:47:18 | Nested Loop trouble : Execution time increases more 1000 time (long) |
Previous Message | bm\mbn | 2005-09-16 11:51:43 | RAID Stripe size |