Re: foreign key constraint, planner ignore index.

Richard Huxton wrote:
>
> I'm putting together a small test case to see if I can reproduce your
> behaviour here.

Does the attached small script misbehave in the same way as your real
data? From here it works fine when the fkey is ON ... RESTRICT.

I'm right in thinking that your "nodes" fkey is RESTRICT on update and
delete?

--
   Richard Huxton
   Archonet Ltd
CREATE TABLE testnode (node integer, nodename text, PRIMARY KEY (node));
CREATE TABLE traffic (id SERIAL, node integer NOT NULL, ts TIMESTAMP(0) WITH TIME ZONE NOT NULL, msg text);
INSERT INTO testnode SELECT s, 'node number ' || s FROM generate_series(1,25) s;
INSERT INTO traffic (id, node, ts, msg) SELECT s, 11, 'epoch'::timestamptz + s * '1 second'::interval, null FROM
generate_series(1,9999999)s; 

ALTER TABLE traffic ADD CONSTRAINT traffic_node_fkey FOREIGN KEY (node) REFERENCES testnode (node) ON UPDATE RESTRICT
ONDELETE RESTRICT; 
CREATE INDEX traffic_node_idx ON traffic (node);


BEGIN;
EXPLAIN ANALYSE DELETE FROM testnode WHERE node=9;
ROLLBACK;

BEGIN;
EXPLAIN ANALYSE DELETE FROM testnode WHERE node=11;
ROLLBACK;