Re: foreign key constraint, planner ignore index.
От | Andrew Nesheret |
---|---|
Тема | Re: foreign key constraint, planner ignore index. |
Дата | |
Msg-id | 476A7EC0.7080104@infinet.ru обсуждение исходный текст |
Ответ на | Re: foreign key constraint, planner ignore index. (Richard Huxton <dev@archonet.com>) |
Ответы |
Re: foreign key constraint, planner ignore index.
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Следующее
От: Ivan Sergio BorgonovoДата:
Сообщение: Re: referential integrity and defaults, DB design or trick