pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Meetesh Karia <meetesh.karia@gmail.com>
To: Christian Compagnon <ccompagnon@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Query take 101 minutes, help, please
Date: Wed, 7 Sep 2005 19:09:39 +0200
Message-ID: <fc5b04ca0509071009388d72f1@mail.gmail.com> (raw)
In-Reply-To: <d20db5e605090709227c2c3596@mail.gmail.com>
References: <d20db5e605090709227c2c3596@mail.gmail.com>

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 could also compare the performance of that to this and choose the one 
that works the best:

SELECT "Rut Cliente"
FROM "Internet_Abril" a
LEFT JOIN "Internet_Enero" e ON a."Rut Cliente" = e."Rut Cliente"
LEFT JOIN "Internet_Febrero" f ON a."Rut Cliente" = f."Rut Cliente"
LEFT JOIN "Internet_Marzo" m ON a."Rut Cliente" = m."Rut Cliente"
WHERE e."Rut Cliente" IS NULL AND f."Rut Cliente" IS NULL and m."Rut 
Cliente" IS NULL;

Meetesh

On 9/7/05, Christian Compagnon <ccompagnon@gmail.com> wrote:
> 
> Hello,
> 
> I'm a newbie in postgresql, I've installed it on a Windows XP machine
> ( I can't use linux, it's a company machine ), I'm courious why this
> query takes so long
> 
> SELECT "Rut Cliente"
> FROM "Internet_Abril"
> WHERE "Rut Cliente" NOT IN ((SELECT "Rut Cliente" FROM
> "Internet_Enero") UNION (SELECT "Rut Cliente" FROM
> "Internet_Febrero") UNION (SELECT "Rut Cliente" FROM
> "Internet_Marzo"));
> 
> it takes about 100 minutes to complete the query.
> All tables has index created ( Rut Cliente is a VarChar ), and tables
> has 50.000 records each.
> 
> The explain for the query tells the following
> 
> "QUERY PLAN
> Seq Scan on "Internet_Abril" (cost=19406.67..62126112.70 rows=24731 
> width=13)
> Filter: (NOT (subplan))
> SubPlan
> -> Materialize (cost=19406.67..21576.07 rows=136740 width=13)
> -> Unique (cost=17784.23..18467.93 rows=136740 width=13)
> -> Sort (cost=17784.23..18126.08 rows=136740 width=13)
> Sort
> Key: "Rut Cliente"
> -> Append (cost=0.00..3741.80 rows=136740 width=13)
> -> Subquery Scan "*SELECT* 1" (cost=0.00..1233.38
> rows=45069 width=13)
> -> Seq Scan on "Internet_Enero" (cost=0.00..782.69
> rows=45069 width=13)
> -> Subquery Scan "*SELECT* 2" (cost=0.00..1104.06
> rows=40353 width=13)
> -> Seq Scan on "Internet_Febrero" (cost=0.00..700.53
> rows=40353 width=13)
> -> Subquery Scan "*SELECT* 3" (cost=0.00..1404.36
> rows=51318 width=13)
> -> Seq Scan on "Internet_Marzo" (cost=0.00..891.18
> rows=51318 width=13)
> 
> Any help will be apreciated, It's for my thesis
> 
> 
> saludos
> Christian
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

view thread (5+ messages)  latest in thread

Message-ID: <fc5b04ca0509071009388d72f1@mail.gmail.com>
Permalink:  ../fc5b04ca0509071009388d72f1@mail.gmail.com/
Also on:    postgresql.org/message-id/fc5b04ca0509071009388d72f1@mail.gmail.com

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: meetesh.karia@gmail.com, ccompagnon@gmail.com
  Subject: Re: Query take 101 minutes, help, please
  In-Reply-To: <fc5b04ca0509071009388d72f1@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox