Date: Sat, 26 Mar 2005 07:55:39 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Karim Nassar <karim.nassar@acm.org>
Cc: Simon Riggs <simon@2ndquadrant.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Christopher Kings-Lynne <chriskl@familyhealth.com.au>,
	pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <1111787264.9168.7.camel@k2.cet.nau.edu>
Message-ID: <20050326075020.P63597@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>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII


On Fri, 25 Mar 2005, Karim Nassar wrote:

> On Fri, 2005-03-25 at 15:10 +0000, Simon Riggs wrote:
> > Karim: Did this happen? If not, can you drop and re-create and confirm
> > that you get the WARNING? If not, we have problems.
>
> No. Nor do I think that I should. SERIAL is shortcut for INTEGER, no? I
> think there is some other (TBD) problem causing my big seq scan.
>
> orfs=# ALTER TABLE measurement DROP CONSTRAINT measurement_id_int_sensor_meas_type_fkey;
> ALTER TABLE
> orfs=# ALTER TABLE ONLY measurement ADD CONSTRAINT measurement_id_int_sensor_meas_type_fkey
> orfs-#      FOREIGN KEY (id_int_sensor_meas_type) REFERENCES int_sensor_meas_type(id_int_sensor_meas_type);
> ALTER TABLE
> orfs=#
>
> The add constraint statement comes directly from a pg_dump.
>
> For clarity, the table/indexes were created as such:
>
> CREATE TABLE int_sensor_meas_type(
>     id_int_sensor_meas_type SERIAL PRIMARY KEY,
>     id_sensor integer NOT NULL REFERENCES sensor,
>     id_meas_type integer NOT NULL REFERENCES meas_type UNIQUE);
>
> CREATE TABLE measurement (
>     id_measurement SERIAL PRIMARY KEY,
>     id_int_sensor_meas_type integer NOT NULL REFERENCES int_sensor_meas_type,
>     datetime timestamp WITH TIME ZONE NOT NULL,
>     value numeric(15,5) NOT NULL,
>     created timestamp with time zone NOT NULL DEFAULT now(),
>     created_by TEXT NOT NULL REFERENCES public.person(id_person));
>
> CREATE INDEX measurement__id_int_sensor_meas_type_idx ON measurement(id_int_sensor_meas_type);

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?