Hi all, Dave Page wrote:
I did some extensive testing using PostgreSQL 7.3.1 (logs and results available upon request), and the massive slowdown is NOT related to qualified tablename syntax or (lack of) VACUUM ANALYZE, but to the following change :If you really think the schema qualification has something to do with it, try issuing the ADD FOREIGN KEY command manually in psql, with and without schema name.
Well to be honest I'm having a hard time believing it, but having looked at this in some depth, it's the only thing that the 2 versions of pgAdmin are doing differently. Even the PostgreSQL logs agree with that. I'm relying on Didier for test results though as I don't have a test system I can use for this at the moment. But it gives us something to try - Didier can you create a new database please, and load the data from 2 tables. VACUUM ANALYZE, then add the foreign key in psql using the syntax 1.4.2 uses. Then drop the database, and load exactly the same data in the same way, VACUUM ANALYZE again, and create the fkey using the qualified tablename syntax.
pgAdminII 1.4.2 :
-------------------
CREATE TABLE articles (
article_id integer DEFAULT nextval('"articles_article_id_key"'::text) NOT NULL,
...
test=# \d articles
Table "public.articles"
Column | Type | Modifiers
-----------------+-----------------------+-------------------------------------------------------------
article_id | integer | not null default nextval('"articles_article_id_key"'::text)
...
pgAdminII 1.4.12 :
--------------------
CREATE TABLE articles (
article_id bigint DEFAULT nextval('"articles_article_id_key"'::text) NOT NULL,
...
test=# \d articles
Table "public.articles"
Column | Type | Modifiers
-----------------+-----------------------+-------------------------------------------------------------
article_id | bigint | not null default nextval('"articles_article_id_key"'::text)
...
With two tables each containing some 20.000 entries, the fk creation time between both of them increases from ~ 1.8 secs to ~ 221 secs.
Regards,
Didier
--
Didier Moens
-----
RUG/VIB - Dept. Molecular Biomedical Research - Core IT
tel ++32(9)2645309 fax ++32(9)2645348
http://www.dmb.rug.ac.be
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
