Received: from news.tht.net (news.hub.org [216.126.91.242]) by hub.org (8.10.1/8.10.1) with ESMTP id e7LHtHT45058 for ; Mon, 21 Aug 2000 13:55:17 -0400 (EDT) Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id NAA65644 for pgsql-sql@postgresql.org; Mon, 21 Aug 2000 13:54:57 -0400 (EDT) (envelope-from news) X-Authentication-Warning: news.tht.net: news set sender to using -f From: "Franz J Fortuny" X-Newsgroups: comp.databases.postgresql.sql Subject: Speed or configuration Lines: 61 MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Message-ID: <92eo5.45146$2k1.1270827@news-west.usenetserver.com> X-Abuse-Info: Please be sure to forward a copy of ALL headers X-Abuse-Info: Otherwise we will be unable to process your complaint properly X-Complaints-To: support@usenetserver.com Organization: WebUseNet Corp http://www.usenetserver.com - Home of the fastest NNTP servers on the Net. Date: Mon, 21 Aug 2000 12:53:02 -0500 To: pgsql-sql@postgresql.org X-Archive-Number: 200008/208 The Hermit Hacker wrote: "use cut-n-paste please, and send us the results of the EXPLAIN ... stuff like the cost estimates and whatnot tell us *so* much ..." This is it: exis=# \d pvdprcod NUMART | integer | not null NUMDEP | smallint | not null NUMPRO | smallint | not null MODELO | varchar(20) | not null TALLA | varchar(4) | not null COLOR | varchar(3) | not null exis=# \d venart cvetda | smallint | not null numdep | smallint | not null numart | integer | not null mes | smallint | not null anio | integer | not null tipotr | varchar(2) | importe | float8 | cantidad | float8 | exis=# explain select cvetda,anio,mes,sum(importe),sum(cantidad) exis-# from venart where numart in exis-# (select "NUMART" from pvdprcod where "NUMDEP"=7 and "NUMPRO"=108) exis-# group by cvetda,numdep,anio,mes; NOTICE: QUERY PLAN: Aggregate (cost=79015875401357.48..79015875413208.91 rows=79010 width=26) -> Group (cost=79015875401357.48..79015875409258.44 rows=790095 width=26) -> Sort (cost=79015875401357.48..79015875401357.48 rows=790095 width=26) -> Seq Scan on venart (cost=100000000.00..79015875283591.09 rows=790095 width=26) SubPlan -> Materialize (cost=100007942.42..100007942.42 rows=34 width=4) -> Seq Scan on pvdprcod (cost=100000000.00..100007942.42 rows=34 width=4) EXPLAIN Why Seq Scan if indexes have been created on the columns used for access? Best regards, Franz J Fortuny