Home > mailing lists

nested query too expensive - Mailing list pgsql-admin

From	Sub Director - Sistemas Informáticos
Subject	nested query too expensive
Date	May 15, 2003 12:22:54
Msg-id	003f01c31ae4$b15a59e0$db00a8c0@219 Whole thread Raw
List	pgsql-admin

Tree view

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

pgsql-admin by date:

From: Robert Treat
Date: 15 May 2003, 12:14:46
Subject: Re: problems with moving a database

From: Juan Miguel
Date: 15 May 2003, 12:33:16
Subject: Postgresql suddenly shutdown. Please help

nested query too expensive - Mailing list pgsql-admin

Previous

Next