Re: The most efficient way to put this?

Le Mardi 5 Mars 2002 11:11, Arsalan Zaidi a écrit :
> SELECT DISTINCT ON (aa.a) aa.a, aa.b, aa.c FROM aa WHERE aa.a NOT IN
> (select zz.a from zz);

1) LEFT JOIN
SELECT DISTINCT ON (aa.a) aa.a, aa.b, aa.c
FROM aa
LEFT JOIN zz ON zz.a = aa.a
WHERE zz.a IS NULL;

Run the query twice and VACUUM ANALYSE the database. Re-run. Is it faster
now? Make sure the required fields are indexed.

2) TRIGGER
Another solution if is to add a boolean field in aa and

- create a trigger on aa which stores the result of "NOT IN (select zz.a FROM
zz)". Then, select queries only rely on table aa, which is "lightning fast".
This is the kind of queries where PostgreSQL can be 10 times faster than
MySQL.

- create a trigger on bb which updates calculated values in aa when bb values
are updated.

The trigger solution is only possible if one of the two tables does not
change ofter (its values are not altered continuously).

Cheers,
Jean-Michel POURE