Re: [ADMIN] nested query too expensive

[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