X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id DBDBE538BF for ; Sat, 26 Mar 2005 23:18:17 +0000 (GMT) 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 70840-01 for ; Sat, 26 Mar 2005 23:18:16 +0000 (GMT) Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by svr1.postgresql.org (Postfix) with ESMTP id AC781538DE for ; Sat, 26 Mar 2005 23:18:16 +0000 (GMT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 3238F358B9; Sat, 26 Mar 2005 15:18:16 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 307DA3537A; Sat, 26 Mar 2005 15:18:16 -0800 (PST) Date: Sat, 26 Mar 2005 15:18:16 -0800 (PST) From: Stephan Szabo To: Karim Nassar Cc: Simon Riggs , Tom Lane , Christopher Kings-Lynne , pgsql-performance@postgresql.org Subject: Re: Delete query takes exorbitant amount of time In-Reply-To: <1111865788.9172.13.camel@k2.cet.nau.edu> Message-ID: <20050326151658.D82144@megazone.bigpanda.com> References: <1111709457.9085.127.camel@k2.cet.nau.edu> <2630.1111711927@sss.pgh.pa.us> <1111715131.9085.174.camel@k2.cet.nau.edu> <3750.1111715318@sss.pgh.pa.us> <4243879B.10200@familyhealth.com.au> <20845.1111733900@sss.pgh.pa.us> <1111763448.11750.730.camel@localhost.localdomain> <1111787264.9168.7.camel@k2.cet.nau.edu> <20050326075020.P63597@megazone.bigpanda.com> <1111865788.9172.13.camel@k2.cet.nau.edu> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/588 X-Sequence-Number: 11293 On Sat, 26 Mar 2005, Karim Nassar wrote: > On Sat, 2005-03-26 at 07:55 -0800, Stephan Szabo wrote: > > That seems like it should be okay, hmm, what does something like: > > > > PREPARE test(int) AS SELECT 1 from measurement where > > id_int_sensor_meas_type = $1 FOR UPDATE; > > EXPLAIN ANALYZE EXECUTE TEST(1); > > > > give you as the plan? > > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------- > Seq Scan on measurement (cost=0.00..164559.16 rows=509478 width=6) > (actual time=11608.402..11608.402 rows=0 loops=1) > Filter: (id_int_sensor_meas_type = $1) > Total runtime: 11608.441 ms > (3 rows) Hmm, has measurement been analyzed recently? You might want to see if raising the statistics target on measurement.id_int_sensor_meas_type and reanalyzing changes the estimated rows down from 500k.