Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-20 Thread Melton Low
How about DELETE FROM T1 WHERE T1.PK IN (SELECT T1.PK FROM T1 EXCEPT SELECT T2.FK FROM T2); Mel On Tue, Oct 20, 2009 at 7:59 AM, Tom Lane wrote: > Shaul Dar writes: > > I assume this will work but will take a long time: > > > DELETE * FROM T1 where T1.PK NOT IN > > (SELECT T1.PK FROM T1, T2 wh

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-20 Thread Tom Lane
Shaul Dar writes: > I assume this will work but will take a long time: > DELETE * FROM T1 where T1.PK NOT IN > (SELECT T1.PK FROM T1, T2 where T1.PK = T2.FK) Well, yeah, but it's unnecessarily inefficient --- why not just DELETE FROM T1 where T1.PK NOT IN (SELECT T2.FK FROM T2) However, that s

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-20 Thread A. Kretschmer
In response to Shaul Dar : > Hi, > > I have two large tables T1 and T2, such that T2 has a FK to T1 (i.e. T2.FK --> > T1.PK, possibly multiple T2 rows may reference the same T1 row). I have > deleted > about 2/3 of table T2. I now want to delete all rows in T1 that are not > referenced by T2, i.e

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-20 Thread Shaul Dar
How about: DELETE * FROM T1 LEFT JOIN T2 ON T1.PK = T2.FK WHERE T2.FK IS NULL Shaul On Tue, Oct 20, 2009 at 2:37 PM, Shaul Dar wrote: > Hi, > > I have two large tables T1 and T2, such that T2 has a FK to T1 (i.e. T2.FK--> > T1.PK, possibly multiple T2 rows may r

[PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-20 Thread Shaul Dar
Hi, I have two large tables T1 and T2, such that T2 has a FK to T1 (i.e. T2.FK--> T1.PK, possibly multiple T2 rows may reference the same T1 row). I have deleted about 2/3 of table T2. I now want to delete all rows in T1 that are not referenced by T2, i.e. all rows in T1 that cannot join with (any

Re: [PERFORM] Domain vs table

2009-10-20 Thread Merlin Moncure
On Sun, Oct 11, 2009 at 11:31 AM, Michal Szymanski wrote: > I think I've found answer to my question > http://www.commandprompt.com/blogs/joshua_drake/2009/01/fk_check_enum_or_domain_that_is_the_question/ > I mostly agree with the comments on the blog but let me throw a couple more points out the

Re: [PERFORM] Performance with sorting and LIMIT on partitioned table

2009-10-20 Thread Joe Uhl
On Mon, Oct 19, 2009 at 6:58 AM, Joe Uhl wrote: I have a similar, recent thread titled Partitioned Tables and ORDER BY with a decent break down. I think I am hitting the same issue Michal is. Essentially doing a SELECT against the parent with appropriate constraint columns in the WHERE clause