pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
nested 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>
  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 15:08  Stephan Szabo <sszabo@megazone23.bigpanda.com>
  parent: Sub Director - Sistemas Informáticos <ecaillava@interlap.com.ar>
  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