Thread: setting up foreign keys
Hi all, This is my first post to the performance list, I hope someone can help me. I'm setting up a table with 2 columns, both of which reference a column in another table: CREATE TABLE headwords_core_lexemes ( core_id int REFERENCES headwords_core(core_id), lexeme_id int REFERENCES headwords_core(core_id), ); Trouble is, it's taken 18 hours and counting! The table headwords_core only has about 13,000 lines, and core_id is the primary key on that table. However, I assume it must be those 13,000 lines that are the problem, since if I try it referencing a similar table with 360 lines the new table is created almost instantly. I found a post on a similar subject from quite a while ago, but no answer, and that was for millions of rows anyway. I only have 13,000. Surely it should be faster than this? Is there a way to speed it up? Sue Fitt
Sue Fitt wrote: > Hi all, > > This is my first post to the performance list, I hope someone can help me. > > I'm setting up a table with 2 columns, both of which reference a column > in another table: > > CREATE TABLE headwords_core_lexemes ( > core_id int REFERENCES headwords_core(core_id), > lexeme_id int REFERENCES headwords_core(core_id), > ); One problem here is both of these are referencing the same column ;) I'm sure that's a typo. It sounds like you have something blocking or locking the other table. Check pg_locks (I think it is), 13,000 rows shouldn't take *that* long. Make sure there is an index on headwords_core(core_id) and whatever the other column should be. Foreign keys have to check the other table so without those indexes, it will be slow(er). -- Postgresql & php tutorials http://www.designmagick.com/
Thanks Chris and Chris, you've solved it. I had a gui open that connects to the database. It was doing nothing (and not preventing me adding to or altering headwords_core via psql), but having closed it the table is instantly created. Weird. BTW, referencing the same column twice is deliberate, it's a cross-reference. Sue Chris Mair wrote: >> This is my first post to the performance list, I hope someone can help me. >> >> I'm setting up a table with 2 columns, both of which reference a column in another table: >> >> CREATE TABLE headwords_core_lexemes ( >> core_id int REFERENCES headwords_core(core_id), >> lexeme_id int REFERENCES headwords_core(core_id), >> ); >> >> Trouble is, it's taken 18 hours and counting! The table headwords_core only has about 13,000 lines, and core_id is the primary key on that table. However, I assume it must be those 13,000 lines that are the problem, since if I try it referencing a similar table with 360 lines the new table is created almost instantly. >> > > Hi, > > the 13000 rows in headwords_core don't matter at all for what this > statement concerns. I bet you have another idle transaction that keeps > headwords_core locked, for example because you did an > alter table headwords_core there... > > Bye, > Chris. > >
Sue Fitt wrote: > Thanks Chris and Chris, you've solved it. > > I had a gui open that connects to the database. It was doing nothing > (and not preventing me adding to or altering headwords_core via psql), > but having closed it the table is instantly created. Weird. > > BTW, referencing the same column twice is deliberate, it's a > cross-reference. The same column and the same table? Same column different table I could understand but not the same column & table ;) I'm sure there's a reason for it though :) -- Postgresql & php tutorials http://www.designmagick.com/
Well they don't necessarily have the same value!
It's a dictionary with cross-referenced words, e.g. 'bring' and
'brought' are both headwords in the dictionary, but 'brought' is
cross-referenced to 'bring'. So, the table stores the information (using
integer id's rather than words) that
bring: bring
brought: see bring
sing: sing
sang: see sing
etc.
Sue
Chris wrote:
> Sue Fitt wrote:
>> Thanks Chris and Chris, you've solved it.
>>
>> I had a gui open that connects to the database. It was doing nothing
>> (and not preventing me adding to or altering headwords_core via
>> psql), but having closed it the table is instantly created. Weird.
>>
>> BTW, referencing the same column twice is deliberate, it's a
>> cross-reference.
>
> The same column and the same table?
>
> Same column different table I could understand but not the same column
> & table ;)
>
> I'm sure there's a reason for it though :)
>
On 8/10/06, Chris <dmagick@gmail.com> wrote: > Sue Fitt wrote: > > Thanks Chris and Chris, you've solved it. > > > > I had a gui open that connects to the database. It was doing nothing > > (and not preventing me adding to or altering headwords_core via psql), > > but having closed it the table is instantly created. Weird. > > > > BTW, referencing the same column twice is deliberate, it's a > > cross-reference. > > The same column and the same table? > > Same column different table I could understand but not the same column & > table ;) create table color(color text); create table person(eye_color text references color(color), hair_color text references color(color)); ;) merlin
On Thu, 10 Aug 2006, Sue Fitt wrote: > Hi all, > > This is my first post to the performance list, I hope someone can help me. > > I'm setting up a table with 2 columns, both of which reference a column > in another table: > > CREATE TABLE headwords_core_lexemes ( > core_id int REFERENCES headwords_core(core_id), > lexeme_id int REFERENCES headwords_core(core_id), > ); > > Trouble is, it's taken 18 hours and counting! What precisely is taking the time, the create table itself? The only thing that the create should be waiting for as far as I know is a lock on headwords_core to add the triggers.
Merlin Moncure wrote: > On 8/10/06, Chris <dmagick@gmail.com> wrote: >> Sue Fitt wrote: >> > Thanks Chris and Chris, you've solved it. >> > >> > I had a gui open that connects to the database. It was doing nothing >> > (and not preventing me adding to or altering headwords_core via psql), >> > but having closed it the table is instantly created. Weird. >> > >> > BTW, referencing the same column twice is deliberate, it's a >> > cross-reference. >> >> The same column and the same table? >> >> Same column different table I could understand but not the same column & >> table ;) > > create table color(color text); > > create table person(eye_color text references color(color), hair_color > text references color(color)); lol. Good point :) *back to the hidey hole!* -- Postgresql & php tutorials http://www.designmagick.com/
Solved, it turned out to be a lock caused by a gui connected to the database, even though the gui wasn't actually doing anything at the time... Sue Stephan Szabo wrote: > On Thu, 10 Aug 2006, Sue Fitt wrote: > > >> Hi all, >> >> This is my first post to the performance list, I hope someone can help me. >> >> I'm setting up a table with 2 columns, both of which reference a column >> in another table: >> >> CREATE TABLE headwords_core_lexemes ( >> core_id int REFERENCES headwords_core(core_id), >> lexeme_id int REFERENCES headwords_core(core_id), >> ); >> >> Trouble is, it's taken 18 hours and counting! >> > > What precisely is taking the time, the create table itself? The only thing > that the create should be waiting for as far as I know is a lock on > headwords_core to add the triggers. > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > >
Sort of on topic, how many foreign keys in a single table is good v. bad? I realize it's relative to the tables the FK's reference so here's an example: Table A: 300 rows Table B: 15,000,000 rows Table C: 100,000 rows Table E: 38 rows Table F: 9 rows Table G: is partitioned on the FK from Table A and has a FK column for each of the above tables I'm in the process of normalizing the database and have a schema like this in mind. Works wonderfully for SELECT's but haven't gotten the data import process down just yet so I haven't had a chance to put it through it's paces. Depending on the performance of INSERT, UPDATE, and COPY I may drop the FK constraints since my app could enforce the FK checks. TIA. Greg > -----Original Message----- > From: pgsql-performance-owner@postgresql.org > [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Chris > Sent: Thursday, August 10, 2006 6:36 PM > To: Merlin Moncure > Cc: Sue Fitt; pgsql-performance@postgresql.org > Subject: Re: [PERFORM] setting up foreign keys > > Merlin Moncure wrote: > > On 8/10/06, Chris <dmagick@gmail.com> wrote: > >> Sue Fitt wrote: > >> > Thanks Chris and Chris, you've solved it. > >> > > >> > I had a gui open that connects to the database. It was doing > >> > nothing (and not preventing me adding to or altering > headwords_core > >> > via psql), but having closed it the table is instantly > created. Weird. > >> > > >> > BTW, referencing the same column twice is deliberate, it's a > >> > cross-reference. > >> > >> The same column and the same table? > >> > >> Same column different table I could understand but not the same > >> column & table ;) > > > > create table color(color text); > > > > create table person(eye_color text references color(color), > hair_color > > text references color(color)); > > lol. Good point :) > > *back to the hidey hole!* > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org > so that your > message can get through to the mailing list cleanly >
Spiegelberg, Greg wrote: > Sort of on topic, how many foreign keys in a single table is good v. > bad? I realize it's relative to the tables the FK's reference so here's > an example: > > Table A: 300 rows > Table B: 15,000,000 rows > Table C: 100,000 rows > Table E: 38 rows > Table F: 9 rows > Table G: is partitioned on the FK from Table A and has a FK column for > each of the above tables > > I'm in the process of normalizing the database and have a schema like > this in mind. Works wonderfully for SELECT's but haven't gotten the > data import process down just yet so I haven't had a chance to put it > through it's paces. Depending on the performance of INSERT, UPDATE, and > COPY I may drop the FK constraints since my app could enforce the FK > checks. As long as both sides of the FK's are indexed I don't think you'll have a problem with a particular number of FK's per table. -- Postgresql & php tutorials http://www.designmagick.com/
On Thu, Aug 10, 2006 at 10:20:45AM +0100, Sue Fitt wrote: > Well they don't necessarily have the same value! > > It's a dictionary with cross-referenced words, e.g. 'bring' and > 'brought' are both headwords in the dictionary, but 'brought' is > cross-referenced to 'bring'. So, the table stores the information (using > integer id's rather than words) that > bring: bring > brought: see bring > sing: sing > sang: see sing > etc. If that's actually how it's represented (a row for both sing and song) it's denormalized. My rule of thumb is "normalize 'til it hurts, denormalize 'til it works", meaning only denormalize if you need to for performance reasons. In this case, it's certainly possible that performance-wise you're best off denormalized, but you might want to experiment and find out. BTW, the normalized way to store this info would be to only put records in that table for brought and song. > Sue > > Chris wrote: > >Sue Fitt wrote: > >>Thanks Chris and Chris, you've solved it. > >> > >>I had a gui open that connects to the database. It was doing nothing > >>(and not preventing me adding to or altering headwords_core via > >>psql), but having closed it the table is instantly created. Weird. > >> > >>BTW, referencing the same column twice is deliberate, it's a > >>cross-reference. > > > >The same column and the same table? > > > >Same column different table I could understand but not the same column > >& table ;) > > > >I'm sure there's a reason for it though :) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461