John D. Burger napisał(a):
>
> select v1.pkey1, v1.field2, v1.field3, v1.field4
> from view as v1
> join
> (select v2.field1, v2.field2, v2.field3
> from view as v2
> group by v2.field2, v2.field3, v2.field4
> having count(*) = 1)
> using (field2, field3, field4);
>
> This is the one that takes eight hours. :( Another way to express
> what I want is this:
>
> select v1.pkey1, v1.field2, v1.field3, v1.field4
> from view as v1
> where not exists
> (select true from view as v2
> where v1.field2 = v2.field2
> and v1.field3 = v2.field3
> and v1.field4 = v2.field4
> and v1.pkey1 <> v2.pkey1);
>
> That looks like a horrible nested loop, but I suppose I should try it
> to make sure it is indeed slower then the previous query.
>
Hi!
Did you try the second query? I guess I should take consirerably less
time than the first one. Usualy I do "these things" like this...
This is the only possibility for the planner to use indexes. The query
plan you send us shows that are mostly seq scans are used.
Regards,
Marcin Inkielman