X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3D73052D42 for ; Tue, 22 Mar 2005 19:10:10 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 41709-01 for ; Tue, 22 Mar 2005 19:10:08 +0000 (GMT) Received: from ceroriesgo.co.cr (unknown [200.91.71.251]) by svr1.postgresql.org (Postfix) with ESMTP id 8BCAC52A62 for ; Tue, 22 Mar 2005 19:10:07 +0000 (GMT) Received: from [192.168.0.110] by ceroriesgo.co.cr (MDaemon.PRO.v7.2.3.R) with ESMTP id md50000015516.msg for ; Tue, 22 Mar 2005 13:10:41 -0800 Message-ID: <42406D90.7090501@ceroriesgo.co.cr> Date: Tue, 22 Mar 2005 13:10:08 -0600 From: Sabio - PSQL User-Agent: Mozilla Thunderbird 1.0 (X11/20041206) X-Accept-Language: en-us, en MIME-Version: 1.0 To: PostgreSQL Admin Subject: Re: Too slow References: <42402A4B.70703@ceroriesgo.co.cr> <8a80660373bf9494ae545525a4e60bcf@sitening.com> In-Reply-To: <8a80660373bf9494ae545525a4e60bcf@sitening.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Authenticated-Sender: slopez@ceroriesgo.co.cr X-Spam-Processed: ceroriesgo.co.cr, Tue, 22 Mar 2005 13:10:41 -0800 (not processed: message from valid local sender) X-MDRemoteIP: 192.168.0.110 X-Return-Path: slopez_pg@ceroriesgo.co.cr X-MDaemon-Deliver-To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.187 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/354 X-Sequence-Number: 16852 WITH: select * from partes where cedula not in (select cedula from sujetos) Seq Scan on partes (cost=0.00..168063925339.69 rows=953831 width=109) Filter: (NOT (subplan)) SubPlan -> Seq Scan on sujetos (cost=0.00..162348.43 rows=5540143 width=15) WITH: select * from partes where not exists (select cedula from sujetos where cedula=partes.cedula) Seq Scan on partes (cost=0.00..7373076.94 rows=953831 width=109) Filter: (NOT (subplan)) SubPlan -> Index Scan using sujetos_pkey on sujetos (cost=0.00..3.84 rows=1 width=15) Index Cond: ((cedula)::text = ($0)::text) Thomas F. O'Connell wrote: > Please post the results of that query as run through EXPLAIN ANALYZE. > > Also, I'm going to reply to this on pgsql-performance, which is > probably where it better belongs. > > -tfo > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-260-0005 > > On Mar 22, 2005, at 8:23 AM, Sabio - PSQL wrote: > >> How can I improve speed on my queries. For example this query takes >> one day executing itself and it has not finalized !!! >> "create table tmp_partes as select * from partes where identificacion >> not in (select cedula from sujetos)" >> >> partes have 1888000 rows, an index on identificacion >> sujetos have 5500000 rows, an index on cedula > > > >