How can this be?
От | Martin Nickel |
---|---|
Тема | How can this be? |
Дата | |
Msg-id | pan.2005.09.17.01.34.11.467862@portant.com обсуждение исходный текст |
Ответы |
Re: How can this be?
Re: How can this be? |
Список | 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;
В списке pgsql-performance по дате отправления: