X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 5A10BD8B43 for ; Mon, 19 Sep 2005 20:04:22 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 43522-04 for ; Mon, 19 Sep 2005 23:04:15 +0000 (GMT) Received: from gp.word-to-the-wise.com (gp.word-to-the-wise.com [64.71.176.18]) by svr1.postgresql.org (Postfix) with ESMTP id 87390D88C4 for ; Mon, 19 Sep 2005 20:04:16 -0300 (ADT) Received: by gp.word-to-the-wise.com (Postfix, from userid 500) id 9393E8FC095; Mon, 19 Sep 2005 16:02:09 -0700 (PDT) Date: Mon, 19 Sep 2005 16:02:09 -0700 From: Steve Atkins To: pgsql-performance@postgresql.org Subject: Re: How can this be? Message-ID: <20050919230209.GA22764@gp.word-to-the-wise.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.1i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.88 required=5 tests=[AWL=-0.496, DNS_FROM_RFC_POST=1.376] X-Spam-Level: X-Archive-Number: 200509/205 X-Sequence-Number: 14513 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