Received: from news.tht.net (news.hub.org [216.126.91.242]) by hub.org (8.10.1/8.10.1) with ESMTP id e7NGtQN83420 for ; Wed, 23 Aug 2000 12:55:27 -0400 (EDT) Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id MAA03498 for pgsql-sql@postgresql.org; Wed, 23 Aug 2000 12:44:17 -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 References: <92eo5.45146$2k1.1270827@news-west.usenetserver.com> Subject: Re: Speed or configuration Lines: 149 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0068_01C00CF7.37D73500" 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: 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: Wed, 23 Aug 2000 11:42:29 -0500 To: pgsql-sql@postgresql.org X-Archive-Number: 200008/257 This is a multi-part message in MIME format. ------=_NextPart_000_0068_01C00CF7.37D73500 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Any light on this subject? 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=3D# \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=3D# \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=3D# explain select cvetda,anio,mes,sum(importe),sum(cantidad) exis-# from venart where numart in exis-# (select "NUMART" from pvdprcod where "NUMDEP"=3D7 and "NUMPRO"=3D108) exis-# group by cvetda,numdep,anio,mes; NOTICE: QUERY PLAN: Aggregate (cost=3D79015875401357.48..79015875413208.91 rows=3D79010 width=3D26) -> Group (cost=3D79015875401357.48..79015875409258.44 rows=3D790095 width=3D26) -> Sort (cost=3D79015875401357.48..79015875401357.48 rows=3D790095 width=3D26) -> Seq Scan on venart (cost=3D100000000.00..79015875283591.09 rows=3D790095 width=3D26) SubPlan -> Materialize (cost=3D100007942.42..100007942.42 rows=3D34 width=3D4) -> Seq Scan on pvdprcod (cost=3D100000000.00..100007942.42 rows=3D34 width=3D4) EXPLAIN Why Seq Scan if indexes have been created on the columns used for access? Best regards, Franz J Fortuny ------=_NextPart_000_0068_01C00CF7.37D73500 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
Any light on this subject?
 
The Hermit Hacker wrote:

"use cut-n-paste please, and send us t= he=20 results of the
EXPLAIN ... stuff
like the cost estimates and whatnot = tell=20 us *so* much
..."

This is it:

exis=3D# \d=20 pvdprcod
 NUMART    | integer    = ; |=20 not null
 NUMDEP    | smallint    | n= ot=20 null
 NUMPRO    | smallint    | not= =20 null
 MODELO    | varchar(20) | not=20 null
 TALLA     | varchar(4)  | not=20 null
 COLOR     | varchar(3)  | not=20 null

exis=3D# \d venart
 cvetda    |=20 smallint   | not null
 numdep    |=20 smallint   | not null
 numart    |=20 integer    | not=20 null
 mes       | smallint  = ; |=20 not null
 anio      | integer  &= nbsp;=20 | not null
 tipotr    | varchar(2)=20 |
 importe   | float8    =20 |
 cantidad  | float8     |

exis=3D= #=20 explain select
cvetda,anio,mes,sum(importe),sum(cantidad)
exis-# from= =20 venart where numart in
exis-# (select "NUMART" from pvdprcod where=20 "NUMDEP"=3D7
and "NUMPRO"=3D108)
exis-# group by=20 cvetda,numdep,anio,mes;
NOTICE:  QUERY PLAN:

Aggregate = =20 (cost=3D79015875401357.48..79015875413208.91
rows=3D79010 width=3D26) =20 ->  Group =20 (cost=3D79015875401357.48..79015875409258.44
rows=3D790095=20 width=3D26)
        -> =20 Sort
(cost=3D79015875401357.48..79015875401357.48=20 rows=3D790095
width=3D26)
       &= nbsp;     =20 ->  Seq Scan on venart
(cost=3D100000000.00..79015875283591.09= =20 rows=3D790095
width=3D26)
       &= nbsp;           =20 SubPlan
          &nbs= p;          =20 ->  Materialize
(cost=3D100007942.42..100007942.42 rows=3D34=20 width=3D4)
          &= nbsp;           &nbs= p;    =20 ->  Seq Scan on pvdprcod
(cost=3D100000000.00..100007942.42 rows= =3D34=20 width=3D4)

EXPLAIN

Why Seq Scan if indexes have been created = on=20 the
columns used for access?

Best regards,

Franz J=20 Fortuny



------=_NextPart_000_0068_01C00CF7.37D73500--