Re: Delete query takes exorbitant amount of time

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?