Skip site navigation (1) Skip section navigation (2)

REDUCIR COSTE DEL QUERY

From: "SERGIO CRESPO" <screspo(at)rollerstar(dot)com>
To: <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: REDUCIR COSTE DEL QUERY
Date: 2007-07-25 10:49:44
Message-ID: 000b01c7cea9$8418ef50$1301a8c0@SERGIO (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
Saludos a todos:
Tengo una tabla con :
  count  
---------
 1705830 registros

Me gustaría poder reducir el tiempo de consulta 
la definición de la tabla es:
        Tabla «public.plan_mes»
 Columna |     Tipo      | Modificadores 
---------+---------------+---------------
 empresa | character(7)  | not null
 codigo  | character(10) | not null
 mes     | integer       | not null
 debe    | numeric       | 
 haber   | numeric       | 
 n1      | character(1)  | 
 n2      | character(2)  | 
 n3      | character(3)  | 
Índices:
    «plan_mes_pkey» PRIMARY KEY, btree (empresa, codigo, mes)
    «n1_plan_mes» btree (empresa, n1, mes)
    «n2_plan_mes» btree (empresa, n2, mes)
    «n3_plan_mes» btree (empresa, n3, mes)
    «plan_mes_emp_cod» btree (empresa, codigo)


el coste de la consulta es muy alto:
explain select sum(debe) as debe,sum(haber) as haber from plan_mes where                  
  empresa='120071' and codigo like '4305037%' group by mes order by mes ; 

                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 GroupAggregate  (cost=216920.22..216920.24 rows=1 width=20)
   ->  Sort  (cost=216920.22..216920.23 rows=1 width=20)
         Sort Key: mes
         ->  Seq Scan on plan_mes  (cost=0.00..216920.21 rows=1 width=20)
               Filter: ((empresa = '120071'::bpchar) AND (codigo ~~ '4305037%'::text))
(5 filas)

Trabajo con la version 8.0.13 bajo Suse Linux

Gracias


Responses

pgsql-es-ayuda by date

Next:From: Felipe FernandezDate: 2007-07-25 11:08:48
Subject: Re: explain analyze
Previous:From: Felipe FernandezDate: 2007-07-25 10:24:50
Subject: Re: explain analyze

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group