Обсуждение: Foreign key wierdness
Hi all, A pgAdmin user has noticed that Foreign Keys take significantly longer to create when migrating a database in pgAdmin in v1.4.12 than in v1.4.2. The only difference in the migration code between these releases, is that pgAdmin now qualifies tables names with the schema name. The following log snippets are from identical migrations from an Access database to PostgreSQL 7.3.1: pgAdmin 1.4.12 ============== Jan 19 16:34:26 lmb042 postgres[17986]: [78991-1] LOG: query: ALTER TABLE public.articles ADD CONSTRAINT productsarticles_fk FOREIGN KEY(product_ref) REFERENCES public.products Jan 19 16:34:26 lmb042 postgres[17986]: [78991-2] (product_id) ON DELETE CASCADE ON UPDATE CASCADE Jan 19 16:34:26 lmb042 postgres[17986]: [78992] NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) Jan 19 16:34:26 lmb042 postgres[17986]: [78993] LOG: query: SELECT 1 FROM ONLY "public"."products" x WHERE "product_id" = $1 FOR UPDATE OF x Jan 19 16:38:33 lmb042 postgres[17986]: [78994] LOG: duration: 247.585771 sec pgAdmin 1.4.2 ============= Jan 19 15:48:56 lmb042 postgres[17542]: [78991-1] LOG: query: ALTER TABLE articles ADD CONSTRAINT productsarticles_fk FOREIGN KEY(product_ref) REFERENCES products (product_id) Jan 19 15:48:56 lmb042 postgres[17542]: [78991-2] ON DELETE CASCADE ON UPDATE CASCADE Jan 19 15:48:56 lmb042 postgres[17542]: [78992] NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) Jan 19 15:48:56 lmb042 postgres[17542]: [78993] LOG: query: SELECT 1 FROM ONLY "public"."products" x WHERE "product_id" = $1 FOR UPDATE OF x Jan 19 15:48:58 lmb042 postgres[17542]: [78994] LOG: duration: 1.988144 sec A similar select query follows every key creation, but is not issued by pgAdmin, or the user's application(s), so I assume PostgreSQL is doing it for some reason. Any ideas why it takes so long (for the same data) when pgAdmin qualifies the table name in the ALTER statement? Thanks, Dave.
"Dave Page" <dpage@vale-housing.co.uk> writes:
> A pgAdmin user has noticed that Foreign Keys take significantly longer
> to create when migrating a database in pgAdmin in v1.4.12 than in
> v1.4.2.
The only reason ADD FOREIGN KEY would take a long time is if
(a) it has to wait awhile to get exclusive lock on either the referencing or referenced table; and/or
(b) it takes a long time to verify that the existing entries in the referencing table all have matches in the
referencedtable. (that's the behind-the-scenes query you see)
I'm betting that the table was busy, or there was a lot more data
present in the one case, or you hadn't ever vacuumed/analyzed one or
both tables and so a bad plan was chosen for the verification query.
The schema reference is definitely not the issue.
regards, tom lane
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: 20 January 2003 15:28 > To: Dave Page > Cc: PostgreSQL Hackers Mailing List; Didier Moens > Subject: Re: [HACKERS] Foreign key wierdness > > > "Dave Page" <dpage@vale-housing.co.uk> writes: > > A pgAdmin user has noticed that Foreign Keys take > significantly longer > > to create when migrating a database in pgAdmin in v1.4.12 than in > > v1.4.2. > > The only reason ADD FOREIGN KEY would take a long time is if > (a) it has to wait awhile to get exclusive lock on either > the referencing or referenced table; and/or > (b) it takes a long time to verify that the existing entries > in the referencing table all have matches in the referenced table. > (that's the behind-the-scenes query you see) > > I'm betting that the table was busy, or there was a lot more > data present in the one case, or you hadn't ever > vacuumed/analyzed one or both tables and so a bad plan was > chosen for the verification query. The schema reference is > definitely not the issue. Thing is Tom, this issue can be reproduced *every* time, without fail. The difference is huge as well, it's a difference of a couple of seconds, the total migration will take around 1704.67 seconds without schema qualification, and 11125.99 with schema qualification to quote one test run. As I understand it, this has be tried on a test box, and a production box (running RedHat builds of 7.3.1), and is a migration of the same source Access database. 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. If they were issued by another app or user, how come they exactly follow each fkey creation, and are on the reference table of the fkey? Regards, Dave.
"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).
> 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).
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.
regards, tom lane
On Mon, 2003-01-20 at 15:47, Dave Page wrote: > > -----Original Message----- > > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > Sent: 20 January 2003 15:28 > > To: Dave Page > > Cc: PostgreSQL Hackers Mailing List; Didier Moens > > Subject: Re: [HACKERS] Foreign key wierdness > > > > > > "Dave Page" <dpage@vale-housing.co.uk> writes: > > > A pgAdmin user has noticed that Foreign Keys take > > significantly longer > > > to create when migrating a database in pgAdmin in v1.4.12 than in > > > v1.4.2. > > > > The only reason ADD FOREIGN KEY would take a long time is if > > (a) it has to wait awhile to get exclusive lock on either > > the referencing or referenced table; and/or > > (b) it takes a long time to verify that the existing entries > > in the referencing table all have matches in the referenced table. > > (that's the behind-the-scenes query you see) > > > > I'm betting that the table was busy, or there was a lot more > > data present in the one case, or you hadn't ever > > vacuumed/analyzed one or both tables and so a bad plan was > > chosen for the verification query. The schema reference is > > definitely not the issue. > > Thing is Tom, this issue can be reproduced *every* time, without fail. > The difference is huge as well, it's a difference of a couple of > seconds, the total migration will take around 1704.67 seconds without > schema qualification, and 11125.99 with schema qualification to quote > one test run. can you try running ANALYZE (or VACUUM ANALYZE) after importing data but before creating the foreign keys ? > As I understand it, this has be tried on a test box, and a production > box (running RedHat builds of 7.3.1), and is a migration of the same > source Access database. > > 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. does this SELECT ... FOR UPDATE occur only when schemas are used ? > These exactly follow *every* fkey > creation, and are definately not issued by pgAdmin. If they were issued > by another app or user, how come they exactly follow each fkey creation, > and are on the reference table of the fkey? I think Tom was trying to tell that the backend code indeed runs this, but that it should not be that slow. -- Hannu Krosing <hannu@tm.ee>
> -----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.
> -----Original Message----- > From: Hannu Krosing [mailto:hannu@tm.ee] > Sent: 20 January 2003 18:05 > To: Dave Page > Cc: Tom Lane; PostgreSQL Hackers Mailing List; Didier Moens > Subject: Re: [HACKERS] Foreign key wierdness > > > > 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. > > does this SELECT ... FOR UPDATE occur only when schemas are used ? No, in both case, just significantly more slowly with schema qualification. Note that the qualification is added to the ALTER TABLE statement though - PostgreSQL's qualifying the name in the SELECT for every case. Regards, Dave.
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
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: 20 January 2003 21:28 > To: Didier Moens > Cc: Dave Page; PostgreSQL Hackers Mailing List > Subject: Re: [HACKERS] 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. <puzzled> No there isn't. pgAdmin uses the same unqualified SQL in all cases on 7.2.x... Regards, Dave.
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
> -----Original Message-----
> From: Didier Moens [mailto:Didier.Moens@dmb001.rug.ac.be]
> Sent: 22 January 2003 16:05
> To: Dave Page
> Cc: Tom Lane; PostgreSQL Hackers Mailing List
> Subject: Re: [HACKERS] Foreign key wierdness
>
>
> 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,
> ...
I'd never have guessed that one, despite noting that particular change
in the code some time ago. Oh well...
Anyway, that was changed in pgAdmin because I got a couple of complaints
about it's use of int4 for migrated serial/autonumber columns. One user
was migrating some *very* large numbers from a SQL Server.
From what Tom has said in his reponse, I think the answer for you Didier
is to remap your integer columns to int8 instead of int4 and see what
happens. When I get a couple of minutes I will look at putting a Serials
as... Option in the type map.
Regards, Dave.
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > Sent: 22 January 2003 20:30 > To: Didier Moens > Cc: Dave Page; PostgreSQL Hackers Mailing List > Subject: Re: [HACKERS] Foreign key wierdness > > > > 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 ... Does seem like it needs some work, though it could be argued that the columns should be of the same type (which they were of course until I got clever and increased the size of autonumber column that pgAdmin creates!). Anyway, as always, many thanks for your help and putting our testing back on a sensible course after much head scratching!! :-) Regards, Dave.
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>
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
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
Dave Page wrote: >>From what Tom has said in his reponse, I think the answer for you Didier >is to remap your integer columns to int8 instead of int4 and see what >happens. When I get a couple of minutes I will look at putting a Serials >as... Option in the type map. > Thanks Dave, for all of your invested time. I think the value of tools such as pgAdmin, which provide an almost bumpless cross-platform migration path, cannot be underestimated. Regards, Didier -- Didier Moens ----- RUG/VIB - Dept. Molecular Biomedical Research - Core IT http://www.dmb.rug.ac.be