Re: Query take 101 minutes, help, please

On Wed, 7 Sep 2005, Meetesh Karia wrote:

> PG is creating the union of January, February and March tables first and
> that doesn't have an index on it. If you're going to do many queries using
> the union of those three tables, you might want to place their contents into
> one table and create an index on it.
>
> Otherwise, try something like this:
>
> SELECT "Rut Cliente"
> FROM "Internet_Abril"
> WHERE "Rut Cliente" NOT IN (SELECT "Rut Cliente" FROM
> "Internet_Enero")
> AND "Rut Cliente" NOT IN (SELECT "Rut Cliente" FROM
> "Internet_Febrero")
> AND "Rut Cliente" NOT IN (SELECT "Rut Cliente" FROM
> "Internet_Marzo");

You may also wish to try:

SELECT "Rut Cliente"
FROM "Internet_Abril"
WHERE NOT EXISTS
  (SELECT 1 FROM "Internet_Enero"
   WHERE "Internet_Enero"."Rut Cliente"="Internet_Abril"."Rut Cliente")
AND NOT EXISTS
  (SELECT 1 FROM "Internet_Febrero"
   WHERE "Internet_Febrero"."Rut Cliente"="Internet_Abril"."Rut Cliente")
AND NOT EXISTS
  (SELECT 1 FROM "Internet_Marzo"
   WHERE "Internet_Marzo"."Rut Cliente"="Internet_Abril"."Rut Cliente")

which will probably scan the indexes on the January, February and March
indexes once for each row in the April table.