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 <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
