Re: stuck on values in 8.2
От | Tom Allison |
---|---|
Тема | Re: stuck on values in 8.2 |
Дата | |
Msg-id | 5EF630B0-258B-4D57-B150-D45B75DBCDBC@tacocat.net обсуждение исходный текст |
Ответ на | Re: stuck on values in 8.2 (Tom Allison <tom@tacocat.net>) |
Список | pgsql-general |
I think I fixed the rest of my sql statements with the following: insert into tokens (token)select values.token from (values TOKEN_LIST_STRING ) as values(token) left outer join tokens t using (token) where t.token_idx is null insert into user_token(user_idx, token_idx)select $self->{user_idx}, token_idxfrom tokens left outer join (select token_idx from user_token where user_idx = $self->{user_idx}) ut using (token_idx) where ut.token_idx is null and token_idx in ($string) I think does what I was trying to accomplish. At least the little test sql seems to work. interestingly, the time to process has gone from >100s to <1s. On May 12, 2007, at 2:23 PM, Tom Allison wrote: > Thank you very much for all your help!!! > > Solved this one rather nicely: > > my $glue = q{'),(E'}; > > my $string = "(E'" . join($glue, map{quotemeta } @$tokens) . "')"; > > my $sql =<<SQL; > SELECT values.token, t.token_idx, ut.token_idx > FROM > (values TOKEN_LIST_STRING) as values(token) > left outer join tokens t using (token) > left outer join > ( select token_idx from user_token where user_idx = $self-> > {user_idx}) "ut" > using (token_idx) > SQL > > $sql =~ s/TOKEN_LIST_STRING/$string/o; > > ------------------------- > This is something on the order to 10-1000 times faster depending on > the various result sets matching more/less across the various tables. > "very nice..." > > I'm thinking I could do the same thing where I have a lot of inserts. > But this is going to be considerably harder because I don't have > any really good experience with doing this with transactional > integrity. > > From this query, I eventually want to grab all the values.token and > insert them into the token and user_token table. > > I can call: > insert into tokens(token) values(('one'),('two'),('three')); > then call: > insert into user_token(user_idx, token_idx) > select 14, token_idx from tokens where token in > ('one','two','three') > > And that should work. > HOWEVER: > If I have two simultaneous INSERT INTO token(token) queries with > overlapping values I'm going to get into all kinds of trouble with > the integrity constraint on > my index of unique tokens. Typically I'll get an integrity > violation error. > > Or am I looking at something like: > insert into tokens > select .. > values(..) as values(token) > left outer join tokens using (token) > > (Am I getting the hang of this any better?) > > > > On May 12, 2007, at 12:06 PM, Richard Broersma Jr wrote: > >> >> --- Tom Allison <tom@tacocat.net> wrote: >> >>> This is getting really ugly... >>> it won't finish in less than .. minutes. >>> >>> spam=> explain select u2.token_idx, t.token_idx, foo.token from >>> tokens t left outer join user_token u1 using (token_idx), >>> users u left outer join user_token u2 using (user_idx), >>> (values('one'),('want'),('examine'),('three')) as foo(token) >>> left outer join tokens using (token) >>> where u.user_idx = 15; >> >> It looks to me that your query has (3) left joins and (3) implied >> cross-joins. Perhaps reforming >> your query to eliminate the cross-joins with help performance. In >> regard to your tables <tokens> >> and <user_tokens>, in this query you are referring to (2) separate >> instances of these tables when >> a single instance these tables would probably work just fine. i.e. >> >> tokens t vs. tokens, >> user_token u1 vs user_token u2 >> >> Regards, >> Richard Broersma Jr. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster
В списке pgsql-general по дате отправления: