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 6CD4052A7A
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun, 27 Mar 2005 00:42:13 +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 85306-10
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Sun, 27 Mar 2005 00:42:04 +0000 (GMT)
Received: from mailgate3.nau.edu (mailgate3.nau.edu [134.114.96.145])
	by svr1.postgresql.org (Postfix) with ESMTP id 7502B52A68
	for <pgsql-performance@postgresql.org>;
	Sun, 27 Mar 2005 00:42:03 +0000 (GMT)
Received: from CONVERSION-DAEMON.mailgate3.nau.edu by mailgate3.nau.edu
	(PMDF V6.2-X17 #30845) id <0IDZ00201JA2GS@mailgate3.nau.edu> for
	pgsql-performance@postgresql.org; Sat, 26 Mar 2005 17:42:06 -0700 (MST)
Received: from k2.cet.nau.edu (net9-213.cet.nau.edu [134.114.9.213])
	by mailgate3.nau.edu (PMDF V6.2-X17 #30845)
	with ESMTP id <0IDZ008DRJ8S2A@mailgate3.nau.edu>; Sat,
	26 Mar 2005 17:41:16 -0700 (MST)
Date: Sat, 26 Mar 2005 17:44:47 -0700
From: Karim Nassar <karim.nassar@acm.org>
Subject: Re: Delete query takes exorbitant amount of time
In-reply-to: <20050326151658.D82144@megazone.bigpanda.com>
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
Cc: Simon Riggs <simon@2ndquadrant.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
Message-id: <1111884288.27481.6.camel@k2.cet.nau.edu>
MIME-version: 1.0
X-Mailer: Evolution 2.0.2
Content-type: text/plain
Content-transfer-encoding: 7BIT
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>
	<20050326151658.D82144@megazone.bigpanda.com>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200503/589
X-Sequence-Number: 11294

On Sat, 2005-03-26 at 15:18 -0800, Stephan Szabo wrote:
> 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.

orfs=# ALTER TABLE measurement ALTER COLUMN id_int_sensor_meas_type SET STATISTICS 1000;
ALTER TABLE
orfs=# VACUUM FULL ANALYZE VERBOSE;
<snip>
INFO:  free space map: 52 relations, 13501 pages stored; 9760 total pages needed
DETAIL:  Allocated FSM size: 1000 relations + 300000 pages = 1864 kB shared memory.
VACUUM
orfs=# PREPARE test(int) AS SELECT 1 from measurement where
orfs-# id_int_sensor_meas_type = $1 FOR UPDATE;
PREPARE
orfs=# EXPLAIN ANALYZE EXECUTE TEST(1);
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on measurement  (cost=0.00..164559.16 rows=509478 width=6) (actual time=8948.452..8948.452 rows=0 loops=1)
   Filter: (id_int_sensor_meas_type = $1)
 Total runtime: 8948.494 ms
(3 rows)

orfs=# EXPLAIN ANALYZE EXECUTE TEST(1);
                                                     QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Seq Scan on measurement  (cost=0.00..164559.16 rows=509478 width=6) (actual time=3956.616..3956.616 rows=0 loops=1)
   Filter: (id_int_sensor_meas_type = $1)
 Total runtime: 3956.662 ms
(3 rows)



Some improvement. Even better once it's cached. Row estimate didn't
change. Is this the best I can expect? Is there any other optimizations
I am missing?

TIA,

-- 
Karim Nassar
Department of Computer Science
Box 15600, College of Engineering and Natural Sciences
Northern Arizona University,  Flagstaff, Arizona 86011
Office: (928) 523-5868 -=- Mobile: (928) 699-9221