SEO

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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/

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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/

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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
>

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

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

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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

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

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