Thread: nested query too expensive

nested query too expensive

From

Sub Director - Sistemas Informáticos

Date:

15 May 2003, 12:22:54

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 )

         cuitemp_btrim,
         docu_btrim,
         retper_cod_estab,
         retper_cuitempre,
         retper_documento,
         retper_nombre,
         retper_nro_insc,
         retper_tipodoc

________________________________________________

Table "detadj" ( 18.500.000 rows )

         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