Horton

Thread

  1. nested query too expensive

    Sub Director - Sistemas Informáticos <ecaillava@interlap.com.ar> — 2003-05-15T13:20:01Z

    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

  2. Re: [ADMIN] nested query too expensive

    Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2003-05-15T15:08:52Z

    [Moving to -performance, since it's more on topic there]

On Thu, 15 May 2003, [Windows-1252] Sub Director - Sistemas Informticos 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