

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us] 
> Sent: 20 January 2003 16:08
> To: Dave Page
> Cc: PostgreSQL Hackers Mailing List; Didier Moens
> Subject: Re: [HACKERS] Foreign key wierdness 
> 
> 
> "Dave Page" <dpage@vale-housing.co.uk> writes:
> > Thing is Tom, this issue can be reproduced *every* time, 
> without fail.
> 
> And have you vacuumed or analyzed yet?  Or possibly you are 
> short an index or two (you really need indexes on both the 
> referencing and referenced columns).

Didier?

> > I've been looking at his for some time now (couple of weeks 
> or more), 
> > and the only thing I can find is the SELECT ... FOR UPDATE in the 
> > PostgreSQL logs that I quoted. These exactly follow *every* fkey 
> > creation, and are definately not issued by pgAdmin.
> 
> No, I told you: those are the internal verification query (it 
> comes from RI_FKey_check_ins(), if you want to look).

Sorry, brain hiccup.

> 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.

Thanks, Dave.

Mon, 20 Jan 2003 17:01:46 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Re: Foreign key wierdness 
Didier Moens <moensd@xs4all.be> writes:
> I just formally tested on PostgreSQL 7.2.2 (logs sent to Dave), and the 
> results are perfectly inline with those from 7.2.3 : a massive slowdown 
> when upgrading from pgadminII 1.4.2 to 1.4.12.

I thought the complaint involved PG 7.3?  There is no schema syntax in 7.2.

			regards, tom lane

Mon, 20 Jan 2003 16:27:34 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign key wierdness 
Hi all,

Dave Page wrote:

>>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.
>

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 :

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



Wed, 22 Jan 2003 17:04:59 +0100
Didier Moens <Didier.Moens@dmb.rug.ac.be>
Re: Foreign key wierdness
Didier Moens <Didier.Moens@dmb001.rug.ac.be> writes:
> 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 :

> pgAdminII 1.4.2 :
> -------------------
> CREATE TABLE articles (
>     article_id integer DEFAULT 
> nextval('"articles_article_id_key"'::text) NOT NULL,
> ...

> pgAdminII 1.4.12 :
> --------------------
> CREATE TABLE articles (
>     article_id bigint DEFAULT nextval('"articles_article_id_key"'::text) 
> NOT NULL,
> ...

Ah-hah, and I'll bet that the column being linked to this one by the
foreign key constraint is still an integer?

> 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.

Seems odd that the cost would get *that* much worse.  Maybe we need to
look at whether the FK checking queries need to include explicit casts
...

			regards, tom lane

Wed, 22 Jan 2003 15:30:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign key wierdness 
Dear Tom, Dave,


Tom Lane wrote:

>Ah-hah, and I'll bet that the column being linked to this one by the
>foreign key constraint is still an integer?
>

It sure is ; being a PostgreSQL novice (BTW : many thanks to the whole 
of the PG development team for such an excellent product), I got on this 
track by means of 
http://archives.postgresql.org/pgsql-sql/2001-05/msg00395.php .


>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.
>  
>
>
>Seems odd that the cost would get *that* much worse.  Maybe we need to
>look at whether the FK checking queries need to include explicit casts
>...
>

Well, I reproduced the slowdown with some 20 to 30 different tables.
Anyway, glad I could be of some help, albeit only by testing some 
(probably quite meaningless) border cases ...  :)


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



Wed, 22 Jan 2003 23:27:35 +0100
Didier Moens <moensd@xs4all.be>
Re: Foreign key wierdness
Tom Lane kirjutas K, 22.01.2003 kell 22:30:
> Didier Moens <Didier.Moens@dmb001.rug.ac.be> writes:
> > 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 :
> 
> > pgAdminII 1.4.2 :
> > -------------------
> > CREATE TABLE articles (
> >     article_id integer DEFAULT 
> > nextval('"articles_article_id_key"'::text) NOT NULL,
> > ...
> 
> > pgAdminII 1.4.12 :
> > --------------------
> > CREATE TABLE articles (
> >     article_id bigint DEFAULT nextval('"articles_article_id_key"'::text) 
> > NOT NULL,
> > ...
> 
> Ah-hah, and I'll bet that the column being linked to this one by the
> foreign key constraint is still an integer?

This should at least give out a NOTICE or ABORT or generate a functional
index, not a plain one.

> > 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.
> 
> Seems odd that the cost would get *that* much worse.  Maybe we need to
> look at whether the FK checking queries need to include explicit casts
> ...
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
-- 
Hannu Krosing <hannu@tm.ee>

23 Jan 2003 19:44:53 +0200
Hannu Krosing <hannu@tm.ee>
Re: Foreign key wierdness