RE: Very slow query, Help please!

You should rewrite your query using NOT EXISTS:

insert into LTable
select * from STable
where NOT EXISTS (
SELECT L.ID FROM LTable L
WHERE L.ID = ID);

This will use an index scan on LTable. This is also a FAQ item BTW.

Hope that helps,

Mike Mascari
mascarm(at)mascari(dot)com

-----Original Message-----
From: Igor [SMTP:dbmanager(at)osb368(dot)nnov(dot)ru]
Sent: Saturday, April 14, 2001 12:33 AM
To: pgsql-general(at)postgresql(dot)org
Subject: [GENERAL] Very slow query, Help please!

Hi !

Help me please to resolv my problem.

I have two tables . One of them is large (say 100000 records)
with unique index on "ID"
and the second table (5000 records ) which i have to insert
into the first table , but the second table have much records,
which have many duplicate values in "ID" . and this is
the query which i used for insertion:

insert into LTable select * from STable
where ID not in (select ID from LTable )

this query takes much time. and moreover - for big tables
i couldn't got result of query for about an hour, it looks
like so that it is die...

May be there is anoter way for insertion ?

Thanks for any suggestion!

Igor

---------------------------(end of
broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to
majordomo(at)postgresql(dot)org