pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feednested query too expensive
2+ messages / 2 participants
[nested] [flat]
* nested query too expensive
@ 2003-05-15 13:20 Sub Director - Sistemas Informáticos <ecaillava@interlap.com.ar>
2003-05-15 15:08 ` Re: [ADMIN] nested query too expensive Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 1 reply; 2+ messages in thread
From: Sub Director - Sistemas Informáticos @ 2003-05-15 13:20 UTC (permalink / raw)
To: pgsql-admin@postgresql.org
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [ADMIN] nested query too expensive
2003-05-15 13:20 nested query too expensive Sub Director - Sistemas Informáticos <ecaillava@interlap.com.ar>
@ 2003-05-15 15:08 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 2+ messages in thread
From: Stephan Szabo @ 2003-05-15 15:08 UTC (permalink / raw)
To: Sub Director - Sistemas Informáticos <ecaillava@interlap.com.ar>; +Cc: pgsql-performance
[Moving to -performance, since it's more on topic there]
On Thu, 15 May 2003, [Windows-1252] Sub Director - Sistemas Informáticos wrote:
> 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)
If you're doing a condition on a bunch of columns, you might want a
multi-column index, since postgres is only going to use one of the
indexes below I believe and it may not be considered selective enough
on just one of those conditions. And you're doing cross datatype
comparisons, which is likely to screw it up as well (why is tipodoc an
integer in one and a numeric in the other for example?) I'd also say you
might want to consider upgrading to 7.3.x since the explain format looks
like that from 7.2 or earlier. Also explain analyze output would tell us
what is actually taking the time and could be useful as well.
> 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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-05-15 15:08 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-15 13:20 nested query too expensive Sub Director - Sistemas Informáticos <ecaillava@interlap.com.ar>
2003-05-15 15:08 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
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