Re: foreign key constraint, planner ignore index.

Richard Huxton wrote:
>
>
> 1. Try adding another 5 million rows to the test "traffic" table and
> see if that makes any difference. It shouldn't.
Opps.
1. Step
drop table testnode
cascade;

drop table
traffic;

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 FR
OM generate_series(1,15999999)
s;

ALTER TABLE traffic ADD CONSTRAINT traffic_node_fkey FOREIGN KEY (node)
REFERENCES testnode (node) ON UPDATE RESTRICT ON DELETE
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;

--- output ---
inms=> \i fkey_index_prob.sql
psql:fkey_index_prob.sql:1: NOTICE: drop cascades to constraint
traffic_node_fkey on table traffic
DROP TABLE
DROP TABLE
psql:fkey_index_prob.sql:4: NOTICE: CREATE TABLE / PRIMARY KEY will
create implicit index "testnode_pkey" for table "testnode"
CREATE TABLE
psql:fkey_index_prob.sql:5: NOTICE: CREATE TABLE will create implicit
sequence "traffic_id_seq" for serial column "traffic.id"
CREATE TABLE
INSERT 0 25
INSERT 0 15999999
ALTER TABLE
CREATE INDEX
BEGIN
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
width=6) (actual time=45.494..45.509 rows=1 loops=1)
Index Cond: (node = 9)
Trigger for constraint traffic_node_fkey: time=459.164 calls=1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Good.
Total runtime: 656.148 ms
(4 rows)

ROLLBACK
BEGIN
psql:fkey_index_prob.sql:18: ERROR: update or delete on table
"testnode" violates foreign key constraint "traffic_node_fkey" on table
"traffic"
DETAIL: Key (node)=(11) is still referenced from table "traffic".
ROLLBACK

2. Step
Run script again w/o creating data. *NO ANY MODIFICATIONS* to database.

BEGIN;

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

BEGIN;

EXPLAIN ANALYSE DELETE FROM testnode WHERE
node=11;
ROLLBACK;
--- output ----
BEGIN
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
width=6) (actual time=0.041..0.043 rows=1 loops=1)
Index Cond: (node = 9)
Trigger for constraint traffic_node_fkey: time=41469.620 calls=1
~~~~~~~~~~~~~~~~~~~~ BAD
Total runtime: 41497.467 ms
(4 rows)

ROLLBACK
BEGIN
psql:fkey_index_prob.sql:18: ERROR: update or delete on table
"testnode" violates foreign key constraint "traffic_node_fkey" on table
"traffic"
DETAIL: Key (node)=(11) is still referenced from table "traffic".
ROLLBACK
-----
3. Step Recreate data with 4999999 rows (SAME DIFFERENT results on first
execute and second!!!)
First:

CREATE TABLE
INSERT 0 25
INSERT 0 4999999
ALTER TABLE
CREATE INDEX
BEGIN
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------------------
Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
width=6) (actual time=25.050..25.054 rows=1 loops=1)
Index Cond: (node = 9)
Trigger for constraint traffic_node_fkey: time=18.339 calls=1
~~~~~~~~~~~~~~~~~~~~~~ GOOD!
Total runtime: 43.519 ms
(4 rows)

Second:
BEGIN
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------
Index Scan using testnode_pkey on testnode (cost=0.00..8.27 rows=1
width=6) (actual time=0.114..0.116 rows=1 loops=1)
Index Cond: (node = 9)
Trigger for constraint traffic_node_fkey: time=7183.677 calls=1
~~~~~~~~~~~~~~~~~~~ Perfomance degradation!!!
Total runtime: 7183.928 ms
(4 rows)

ROLLBACK

You comments?

>
> 2. Run a "vacuum verbose sf_ipv4traffic" and see if there's a lot of
> dead rows. I shouldn't have thought there are.
No dead rows.

inms=> VACUUM VERBOSE sf_ipv4traffic;
INFO: vacuuming "public.sf_ipv4traffic"
INFO: index "sf_ipv4traffic_pkey" now contains 15795376 row versions in
122709 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 3.88s/0.52u sec elapsed 38.44 sec.
INFO: index "fki_nodes" now contains 15795376 row versions in 34664 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.04s/0.07u sec elapsed 13.34 sec.
INFO: index "sf_ipv4traffic_idx" now contains 15795376 row versions in
60822 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.69s/0.14u sec elapsed 16.71 sec.
INFO: index "sf_ipv4traffic_idx1" now contains 15795376 row versions in
60822 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.90s/0.19u sec elapsed 17.77 sec.
INFO: index "sf_ipv4traffic_idx3" now contains 15795376 row versions in
60822 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.90s/0.19u sec elapsed 16.97 sec.
INFO: index "sf_ipv4traffic_idx4" now contains 15795376 row versions in
43311 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 1.38s/0.13u sec elapsed 12.09 sec.
INFO: "sf_ipv4traffic": found 0 removable, 15795376 nonremovable row
versions in 162839 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 21.91s/4.93u sec elapsed 252.77 sec.
INFO: vacuuming "pg_toast.pg_toast_16555"
INFO: index "pg_toast_16555_index" now contains 0 row versions in 1 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_16555": found 0 removable, 0 nonremovable row versions
in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.01 sec.

--
__________________________________
WBR, Andrew Nesheret ICQ:10518066