pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can this be?
3+ messages / 3 participants
[nested] [flat]
* How can this be?
@ 2005-09-17 01:34 Martin Nickel <martin@portant.com>
2005-09-19 23:02 ` Re: How can this be? Steve Atkins <steve@blighty.com>
2005-09-19 23:07 ` Re: How can this be? Stephan Szabo <sszabo@megazone.bigpanda.com>
0 siblings, 2 replies; 3+ messages in thread
From: Martin Nickel @ 2005-09-17 01:34 UTC (permalink / raw)
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;
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How can this be?
2005-09-17 01:34 How can this be? Martin Nickel <martin@portant.com>
@ 2005-09-19 23:02 ` Steve Atkins <steve@blighty.com>
1 sibling, 0 replies; 3+ messages in thread
From: Steve Atkins @ 2005-09-19 23:02 UTC (permalink / raw)
To: pgsql-performance
On Fri, Sep 16, 2005 at 08:34:14PM -0500, Martin Nickel wrote:
> 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.
When you delete a row from agent PG needs to find any matching rows in
office. Is office large? Is office(office_id) indexed?
> -- 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;
Cheers,
Steve
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: How can this be?
2005-09-17 01:34 How can this be? Martin Nickel <martin@portant.com>
@ 2005-09-19 23:07 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
1 sibling, 0 replies; 3+ messages in thread
From: Stephan Szabo @ 2005-09-19 23:07 UTC (permalink / raw)
To: Martin Nickel <martin@portant.com>; +Cc: pgsql-performance
On Fri, 16 Sep 2005, Martin Nickel wrote:
> 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.
Are there any tables that reference agent or other triggers? My first
guess would be that there's a foreign key check for something else that's
referencing agent.agent_id for which an index scan isn't being used.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-09-19 23:07 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-17 01:34 How can this be? Martin Nickel <martin@portant.com>
2005-09-19 23:02 ` Steve Atkins <steve@blighty.com>
2005-09-19 23:07 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox