X-Original-To: pgsql-admin@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A6E574766FE for ; Thu, 15 May 2003 09:22:53 -0400 (EDT) Received: from postgresql.org ([64.49.215.8]) by localhost (postgresql.org [64.49.215.8:10024]) (amavisd-new) with ESMTP id 06776-10 for ; Thu, 15 May 2003 09:22:21 -0400 (EDT) Received: from smtp2.arnet.com.ar (smtp2.arnet.com.ar [200.45.191.5]) by postgresql.org (Postfix) with SMTP id 161524766E2 for ; Thu, 15 May 2003 09:22:21 -0400 (EDT) Received: (qmail 23230 invoked from network); 15 May 2003 13:14:14 -0000 Received: from unknown (HELO 219) (200.45.224.75) by smtp2.arnet.com.ar with SMTP; 15 May 2003 13:14:12 -0000 Message-ID: <003f01c31ae4$b15a59e0$db00a8c0@219> From: =?Windows-1252?Q?Sub_Director_-_Sistemas_Inform=E1ticos?= To: Subject: nested query too expensive Date: Thu, 15 May 2003 10:20:01 -0300 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_003C_01C31ACB.8BA1B5E0" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2727.1300 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/207 X-Sequence-Number: 8769 This is a multi-part message in MIME format. ------=_NextPart_000_003C_01C31ACB.8BA1B5E0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable This a relatively simple nested query that we try to use, but it finish in = a "seq scan" with a=20 too high cost, so we had to use a little orthodox solution creating a tempo= ral table into the=20 terminal and scanning this table row's one by one making individual querys = for each one. Any body knows how to make the query work in "index scan" mode ? =20 ________________________________________________________ =20 explain select w.*,b.nombre from (select nro_insc,cod_estab,cuitempre,impue= sto,sum(monto_impo) as totret,sum(monto_rete) as suma_rete,tipodoc,documento from detadj where= nro_insc=3D390009 and cod_estab=3D0 and ano=3D2003 and mes=3D4 and per=3D2 and sec=3D0 group= by nro_insc,cod_estab, cuitempre,impuesto,tipodoc,documento) w LEFT OUTER JOIN retper b on (w.tip= odoc=3Db.tipodoc=20 and btrim(w.documento) like btrim(b.documento) and btrim(w.cuitempre) like= btrim(b.cuitempre)=20 and w.nro_insc=3Db.nro_insc and w.cod_estab=3Db.cod_estab)=20 =20 _______________________________________________ =20 TABLES STRUCTURE: =20 Table "retper" ( 180.000 rows ) Column | Type | Modifiers -----------+---------------+----------- tipodoc | integer | documento | character(20) | nombre | character(40) | domicilio | character(40) | puerta | integer | localidad | character(15) | provincia | character(15) | ningbru | character(20) | c_postal | character(8) | cuitempre | character(20) | nro_insc | integer | cod_estab | integer | graba | date | hora | character(4) | opera | integer | puesto | integer | crc | character(4) | Indexes:=20 cuitemp_btrim, docu_btrim, retper_cod_estab, retper_cuitempre, retper_documento, retper_nombre, retper_nro_insc, retper_tipodoc =20 ________________________________________________ Table "detadj" ( 18.500.000 rows ) Column | Type | Modifiers ------------+-----------------------+----------- cuitempre | character varying(20) | sec | numeric(10,0) | per | numeric(10,0) | mes | numeric(10,0) | ano | numeric(10,0) | nro_insc | numeric(10,0) | cod_estab | numeric(10,0) | nobli | character varying(20) | cod_act | character varying(20) | tipo_agen | character varying(1) | monto_impo | double precision | alicuota | double precision | monto_rete | double precision | tipodoc | numeric(10,0) | documento | character varying(20) | impuesto | numeric(10,0) | tipo_dato | numeric(10,0) | id | character varying(11) | tipo_comp | numeric(10,0) | letra | character varying(1) | terminal | numeric(10,0) | numero | character varying(20) | fecha | date | ningbru | character varying(20) | graba | date | hora | character varying(4) | opera | numeric(10,0) | puesto | numeric(10,0) | Indexes:=20 ano_detadj, ano_mes_per, cod_estab, cuitempre, cuitempre_btrim, documento_btrim, impue, mes_detadj, nro_insc_detadj, per_detadj, sec =20 ________________________________________ =20 QUERY: =20 # explain select w.*,b.nombre from (select nro_insc,cod_estab,cuitempre,imp= uesto,sum(monto_impo) as totret,sum(monto_rete) as suma_rete,tipodoc,docume= nto from detadj where nro_insc=3D390009 and cod_estab=3D0 and ano=3D2003 an= d mes=3D4 and per=3D2 and sec=3D0 group by nro_insc,cod_estab,cuitempre,imp= uesto,tipodoc,documento) w LEFT OUTER JOIN retper b on (w.tipodoc=3Db.tipod= oc and btrim(w.documento) like btrim(b.documento) and btrim(w.cuitempre) li= ke btrim(b.cuitempre) ); RESULTS: =20 NOTICE: QUERY PLAN: =20 Nested Loop (cost=3D4999.30..21256.26 rows=3D1 width=3D220) -> Subquery Scan w (cost=3D4999.30..4999.34 rows=3D1 width=3D106) -> Aggregate (cost=3D4999.30..4999.34 rows=3D1 width=3D106) -> Group (cost=3D4999.30..4999.33 rows=3D2 width=3D106) -> Sort (cost=3D4999.30..4999.30 rows=3D2 width=3D106) -> Index Scan using ano_mes_per on detadj (cost= =3D0.00..4999.29 rows=3D2 width=3D106) -> Seq Scan on retper b (cost=3D0.00..9821.23 rows=3D214523 width=3D96) =20 ________________________________________ =20 E. Caillava ------=_NextPart_000_003C_01C31ACB.8BA1B5E0 Content-Type: text/html; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable
This a relatively simple nested query that= we try=20 to use, but it finish in a "seq scan" with a
too high cost, so we had t= o use=20 a little orthodox solution creating a temporal table into the
terminal = and=20 scanning this table row's one by one making individual querys for each= =20 one.
 
Any body knows how to make the query&= nbsp;work=20 in "index scan" mode=20 ?
 
________________________________________________________
=  
explain=20 select w.*,b.nombre from (select=20 nro_insc,cod_estab,cuitempre,impuesto,sum(monto_impo)
 as=20 totret,sum(monto_rete) as suma_rete,tipodoc,documento from detadj where=20 nro_insc=3D390009
 and cod_estab=3D0 and ano=3D2003 and mes=3D4 and= per=3D2 and=20 sec=3D0 group by=20 nro_insc,cod_estab,
 cuitempre,impuesto,tipodoc,documento) w LEFT O= UTER=20 JOIN retper b on (w.tipodoc=3Db.tipodoc
 and btrim(w.documento) li= ke=20 btrim(b.documento) and btrim(w.cuitempre) like btrim(b.cuitempre)
 = ;and=20 w.nro_insc=3Db.nro_insc and w.cod_estab=3Db.cod_estab)=20
 
_______________________________________________
 
= TABLES=20 STRUCTURE:
 
 Table "retper"  ( 180.000 rows )
<= /DIV>
 
  Column   |  &nb= sp; =20 Type      |=20 Modifiers
-----------+---------------+-----------
 tipodoc =  =20 | integer       |
 documento |=20 character(20) |
 nombre    | character(40)=20 |
 domicilio | character(40) |
 puerta    |= =20 integer       |
 localidad | characte= r(15)=20 |
 provincia | character(15) |
 ningbru   |=20 character(20) |
 c_postal  | character(8) =20 |
 cuitempre | character(20) |
 nro_insc  |=20 integer       |
 cod_estab |=20 integer      =20 |
 graba     |=20 date         =20 |
 hora      | character(4) =20 |
 opera     |=20 integer       |
 puesto  &n= bsp;=20 | integer      =20 |
 crc       | character(4) =20 |
Indexes:
       =  =20 cuitemp_btrim,
        =20 docu_btrim,
        =20 retper_cod_estab,
        =20 retper_cuitempre,
        =20 retper_documento,
        =20 retper_nombre,
        =20 retper_nro_insc,
        =20 retper_tipodoc
 
________________________________________________
 
Table "detadj"  ( 18.500.000 rows )
 
   Column  =20 |        =20 Type          |=20 Modifiers
------------+-----------------------+-----------
 cuit= empre =20 | character varying(20) |
 sec      &= nbsp;=20 | numeric(10,0)        =20 |
 per        |=20 numeric(10,0)        =20 |
 mes        |=20 numeric(10,0)        =20 |
 ano        |=20 numeric(10,0)        =20 |
 nro_insc   |=20 numeric(10,0)        =20 |
 cod_estab  |=20 numeric(10,0)        =20 |
 nobli      | character varying(20)=20 |
 cod_act    | character varying(20)=20 |
 tipo_agen  | character varying(1)  |
 monto_im= po |=20 double precision      |
 alicuota &nb= sp; |=20 double precision      |
 monto_rete | doub= le=20 precision      |
 tipodoc   = ; |=20 numeric(10,0)        =20 |
 documento  | character varying(20)=20 |
 impuesto   |=20 numeric(10,0)        =20 |
 tipo_dato  |=20 numeric(10,0)        =20 |
 id         | character= =20 varying(11) |
 tipo_comp  |=20 numeric(10,0)        =20 |
 letra      | character varying(1) = =20 |
 terminal   |=20 numeric(10,0)        =20 |
 numero     | character varying(20)=20 |
 fecha      |=20 date            = ;     =20 |
 ningbru    | character varying(20)=20 |
 graba      |=20 date            = ;     =20 |
 hora       | character varying(4)&= nbsp;=20 |
 opera      |=20 numeric(10,0)        =20 |
 puesto     |=20 numeric(10,0)         |
Indexes:= =20
       =  =20 ano_detadj,
        =20 ano_mes_per,
        =20 cod_estab,
        =20 cuitempre,
        =20 cuitempre_btrim,
        =20 documento_btrim,
        =20 impue,
        =20 mes_detadj,
        =20 nro_insc_detadj,
        =20 per_detadj,
        =20 sec
 
________________________________________
 
QUERY:
 
# exp= lain=20 select w.*,b.nombre from (select=20 nro_insc,cod_estab,cuitempre,impuesto,sum(monto_impo) as totret,sum(monto_r= ete)=20 as suma_rete,tipodoc,documento from detadj where nro_insc=3D390009 and cod_= estab=3D0=20 and ano=3D2003 and mes=3D4 and per=3D2 and sec=3D0 group by=20 nro_insc,cod_estab,cuitempre,impuesto,tipodoc,documento) w LEFT OUTER JOIN= =20 retper b on (w.tipodoc=3Db.tipodoc and btrim(w.documento) like btrim(b.docu= mento)=20 and btrim(w.cuitempre) like btrim(b.cuitempre) );
 

RESULTS:
 
NOTICE:  QU= ERY=20 PLAN:
 
Nested Loop  (cost=3D4999.30..21256.26 rows=3D1=20 width=3D220)
  ->  Subquery Scan w  (cost=3D4999.30..4= 999.34=20 rows=3D1 width=3D106)
        ->&n= bsp;=20 Aggregate  (cost=3D4999.30..4999.34 rows=3D1=20 width=3D106)
          = ;   =20 ->  Group  (cost=3D4999.30..4999.33 rows=3D2=20 width=3D106)
          = ;         =20 ->  Sort  (cost=3D4999.30..4999.30 rows=3D2=20 width=3D106)
          = ;            &n= bsp;  =20 ->  Index Scan using ano_mes_per on detadj  (cost=3D0.00..4999= .29=20 rows=3D2 width=3D106)
->  Seq Scan on retper b  (cost=3D0.0= 0..9821.23=20 rows=3D214523=20 width=3D96)
 
________________________________________
 =
E.=20 Caillava
------=_NextPart_000_003C_01C31ACB.8BA1B5E0--