X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 92EA4DB0D9 for ; Thu, 27 Oct 2005 11:25:25 -0300 (ADT) 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 57747-07 for ; Thu, 27 Oct 2005 14:25:20 +0000 (GMT) Received: from hotmail.com (bay23-f28.bay23.hotmail.com [64.4.22.78]) by svr1.postgresql.org (Postfix) with ESMTP id 45988DAEAC for ; Thu, 27 Oct 2005 11:25:22 -0300 (ADT) Received: from mail pickup service by hotmail.com with Microsoft SMTPSVC; Thu, 27 Oct 2005 07:25:23 -0700 Message-ID: Received: from 196.40.18.42 by by23fd.bay23.hotmail.msn.com with HTTP; Thu, 27 Oct 2005 14:25:23 GMT X-Originating-IP: [196.40.18.42] X-Originating-Email: [sidarlopez@hotmail.com] X-Sender: sidarlopez@hotmail.com From: =?iso-8859-1?B?U2lkYXIgTPNwZXogQ3J1eg==?= To: pgsql-performance@postgresql.org Subject: how postgresql request the computer resources Date: Thu, 27 Oct 2005 08:25:23 -0600 Mime-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1; format=flowed X-OriginalArrivalTime: 27 Oct 2005 14:25:23.0928 (UTC) FILETIME=[44B86580:01C5DB02] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.324 required=5 tests=[AWL=-0.426, DNS_FROM_RFC_ABUSE=0.374, DNS_FROM_RFC_POST=1.376, MSGID_FROM_MTA_HEADER=0] X-Spam-Level: * X-Archive-Number: 200510/417 X-Sequence-Number: 15185 Is there something that tells postgres to take the resorces from computer (RAM, HDD, SWAP on linux) as it need, not modifying variables on postgresql.conf and other operating system things? A days ago i am trying to show that postgres is better than mssql but when execute a simple query like: (1) select count(*) from ( select archivo from fotos except select archivo from archivos ) x; Aggregate (cost=182162.83..182162.84 rows=1 width=0) (actual time=133974.495..133974.498 rows=1 loops=1) -> Subquery Scan x (cost=173857.98..181830.63 rows=132878 width=0) (actual time=109148.158..133335.279 rows=169672 loops=1) -> SetOp Except (cost=173857.98..180501.86 rows=132878 width=58) (actual time=109148.144..132094.382 rows=169672 loops=1) -> Sort (cost=173857.98..177179.92 rows=1328775 width=58) (actual time=109147.656..113870.975 rows=1328775 loops=1) Sort Key: archivo -> Append (cost=0.00..38710.50 rows=1328775 width=58) (actual time=27.062..29891.075 rows=1328775 loops=1) -> Subquery Scan "*SELECT* 1" (cost=0.00..17515.62 rows=523431 width=58) (actual time=27.052..9560.719 rows=523431 loops=1) -> Seq Scan on fotos (cost=0.00..12281.31 rows=523431 width=58) (actual time=27.038..5390.238 rows=523431 loops=1) -> Subquery Scan "*SELECT* 2" (cost=0.00..21194.88 rows=805344 width=58) (actual time=10.803..12117.788 rows=805344 loops=1) -> Seq Scan on archivos (cost=0.00..13141.44 rows=805344 width=58) (actual time=10.784..5420.164 rows=805344 loops=1) Total runtime: 134552.325 ms (2) select count(*) from fotos where archivo not in (select archivo from archivos) Aggregate (cost=29398.98..29398.99 rows=1 width=0) (actual time=26660.565..26660.569 rows=1 loops=1) -> Seq Scan on fotos (cost=15154.80..28744.69 rows=261716 width=0) (actual time=13930.060..25859.340 rows=169799 loops=1) Filter: (NOT (hashed subplan)) SubPlan -> Seq Scan on archivos (cost=0.00..13141.44 rows=805344 width=58) (actual time=0.319..5647.043 rows=805344 loops=1) Total runtime: 26747.236 ms (3) select count(1) from fotos f where not exists (select a.archivo from archivos a where a.archivo=f.archivo) Aggregate (cost=1761354.08..1761354.09 rows=1 width=0) (actual time=89765.384..89765.387 rows=1 loops=1) -> Seq Scan on fotos f (cost=0.00..1760699.79 rows=261716 width=0) (actual time=75.556..88880.234 rows=169799 loops=1) Filter: (NOT (subplan)) SubPlan -> Index Scan using archivos_archivo_idx on archivos a (cost=0.00..13451.40 rows=4027 width=58) (actual time=0.147..0.147 rows=1 loops=523431) Index Cond: ((archivo)::text = ($0)::text) Total runtime: 89765.714 ms (4) SELECT count(*) FROM fotos f LEFT JOIN archivos a USING(archivo) WHERE a.archivo IS NULL Aggregate (cost=31798758.40..31798758.41 rows=1 width=0) (actual time=114267.337..114267.341 rows=1 loops=1) -> Merge Left Join (cost=154143.73..31772412.02 rows=10538550 width=0) (actual time=85472.696..113392.399 rows=169799 loops=1) Merge Cond: ("outer"."?column2?" = "inner"."?column2?") Filter: ("inner".archivo IS NULL) -> Sort (cost=62001.08..63309.66 rows=523431 width=58) (actual time=38018.343..39998.201 rows=523431 loops=1) Sort Key: (f.archivo)::text -> Seq Scan on fotos f (cost=0.00..12281.31 rows=523431 width=58) (actual time=0.158..4904.410 rows=523431 loops=1) -> Sort (cost=92142.65..94156.01 rows=805344 width=58) (actual time=47453.790..50811.216 rows=805701 loops=1) Sort Key: (a.archivo)::text -> Seq Scan on archivos a (cost=0.00..13141.44 rows=805344 width=58) (actual time=0.206..7160.148 rows=805344 loops=1) Total runtime: 114893.116 ms WITH ANY OF THIS QUERIES MSSQL TAKES NOT MUCH OF 7 SECONDS.... PLEASE HELP ME _________________________________________________________________ Consigue aquí las mejores y mas recientes ofertas de trabajo en América Latina y USA: http://latam.msn.com/empleos/