From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Karim Nassar <karim.nassar@acm.org>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
Cc: pgsql-performance@postgresql.org
Subject: Re: Delete query takes exorbitant amount of time
Date: Sat, 26 Mar 2005 07:55:39 -0800 (PST)
Message-ID: <20050326075020.P63597@megazone.bigpanda.com> (raw)
In-Reply-To: <1111787264.9168.7.camel@k2.cet.nau.edu>
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>
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?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sszabo@megazone.bigpanda.com, karim.nassar@acm.org, simon@2ndquadrant.com, tgl@sss.pgh.pa.us, chriskl@familyhealth.com.au
Subject: Re: Delete query takes exorbitant amount of time
In-Reply-To: <20050326075020.P63597@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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