From: Sub Director - Sistemas Informáticos <ecaillava@interlap.com.ar>
To: pgsql-admin@postgresql.org
Subject: nested query too expensive
Date: Thu, 15 May 2003 10:20:01 -0300
Message-ID: <003f01c31ae4$b15a59e0$db00a8c0@219> (raw)
This a relatively simple nested query that we try to use, but it finish in a "seq scan" with a
too high cost, so we had to use a little orthodox solution creating a temporal table into the
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 ?
________________________________________________________
explain select w.*,b.nombre from (select nro_insc,cod_estab,cuitempre,impuesto,sum(monto_impo)
as totret,sum(monto_rete) as suma_rete,tipodoc,documento from detadj where nro_insc=390009
and cod_estab=0 and ano=2003 and mes=4 and per=2 and sec=0 group by nro_insc,cod_estab,
cuitempre,impuesto,tipodoc,documento) w LEFT OUTER JOIN retper b on (w.tipodoc=b.tipodoc
and btrim(w.documento) like btrim(b.documento) and btrim(w.cuitempre) like btrim(b.cuitempre)
and w.nro_insc=b.nro_insc and w.cod_estab=b.cod_estab)
_______________________________________________
TABLES STRUCTURE:
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:
cuitemp_btrim,
docu_btrim,
retper_cod_estab,
retper_cuitempre,
retper_documento,
retper_nombre,
retper_nro_insc,
retper_tipodoc
________________________________________________
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:
ano_detadj,
ano_mes_per,
cod_estab,
cuitempre,
cuitempre_btrim,
documento_btrim,
impue,
mes_detadj,
nro_insc_detadj,
per_detadj,
sec
________________________________________
QUERY:
# explain select w.*,b.nombre from (select nro_insc,cod_estab,cuitempre,impuesto,sum(monto_impo) as totret,sum(monto_rete) as suma_rete,tipodoc,documento from detadj where nro_insc=390009 and cod_estab=0 and ano=2003 and mes=4 and per=2 and sec=0 group by nro_insc,cod_estab,cuitempre,impuesto,tipodoc,documento) w LEFT OUTER JOIN retper b on (w.tipodoc=b.tipodoc and btrim(w.documento) like btrim(b.documento) and btrim(w.cuitempre) like btrim(b.cuitempre) );
RESULTS:
NOTICE: QUERY PLAN:
Nested Loop (cost=4999.30..21256.26 rows=1 width=220)
-> Subquery Scan w (cost=4999.30..4999.34 rows=1 width=106)
-> Aggregate (cost=4999.30..4999.34 rows=1 width=106)
-> Group (cost=4999.30..4999.33 rows=2 width=106)
-> Sort (cost=4999.30..4999.30 rows=2 width=106)
-> Index Scan using ano_mes_per on detadj (cost=0.00..4999.29 rows=2 width=106)
-> Seq Scan on retper b (cost=0.00..9821.23 rows=214523 width=96)
________________________________________
E. Caillava
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: ecaillava@interlap.com.ar, pgsql-admin@postgresql.org
Subject: Re: nested query too expensive
In-Reply-To: <003f01c31ae4$b15a59e0$db00a8c0@219>
References: <003f01c31ae4$b15a59e0$db00a8c0@219>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox