Skip site navigation (1) Skip section navigation (2)

Re: Delete query takes exorbitant amount of time

From: Karim Nassar <karim(dot)nassar(at)NAU(dot)EDU>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Delete query takes exorbitant amount of time
Date: 2005-03-25 02:58:40
Message-ID: 1111719520.9085.190.camel@k2.cet.nau.edu (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 2005-03-24 at 20:48 -0500, Tom Lane wrote:
> In that case there's a datatype mismatch between the referencing and
> referenced columns, which prevents the index from being used for the
> FK check.

Can I have more words on this? Here is how I created the tables:

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);

Do I need to cast the id_int_sensor_meas_type column when creating the
index? Both referrer and referenced look like INTEGER to me...

http://www.postgresql.org/docs/8.0/interactive/datatype.html#DATATYPE-SERIAL
says: "The type names serial and serial4 are equivalent: both create
integer columns" 

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


In response to

pgsql-performance by date

Next:From: Christopher Kings-LynneDate: 2005-03-25 03:37:07
Subject: Re: Delete query takes exorbitant amount of time
Previous:From: Tom LaneDate: 2005-03-25 02:32:56
Subject: Re: Delete query takes exorbitant amount of time

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group