> Date: Mon, 10 Nov 2008 11:36:49 -0400
> From: [EMAIL PROTECTED]
> To: pgsql-es-ayuda@postgresql.org
> Subject: Re: [pgsql-es-ayuda] SELECT muy LENTO
>
> El día 10 de noviembre de 2008 11:09, Edwin Quijada
> escribió:
>>
>> ten go un select el cual usando un indice en una tabla de 3,000,000 dura mas
>> de 30 seg. El problema es que el no usa el indice en esta tabla y se va por
>> una busqueda secuencial
>>
>> Este es el select y el EXPLAIN de cada uno
>> SELECT
>>
>>
>> B.fid_disposition,
>>
>> COUNT(1)
>> FROM
>>
>> aegon.ttransaction_head A,
>>
>> aegon.ttransaction_det B
>> WHERE
>> 1=1
>> AND
>> A.fdate_proccess=NOW()::DATE-90
>> AND
>> b.fdate_proccess=A.fdate_proccess
>> AND
>> B.fsec_doc=A.fsec_doc
>> GROUP BY
>> B.fid_disposition
>>
>> QUERY
>> PLAN
>> HashAggregate
>> (cost=179822.04..179822.05 rows=1 width=8) (actual time=196.783..196.803
>> rows=26
>> loops=1)
>> -> Hash Join
>> (cost=74707.34..179818.22 rows=763 width=8) (actual time=67.516..172.596
>> rows=34075 loops=1)
>> Hash Cond: (b.fsec_doc =
>> a.fsec_doc)
>> -> Bitmap Heap Scan on
>> ttransaction_det b (cost=744.97..104756.35 rows=44887 width=16) (actual
>> time=7.545..36.134 rows=34075 loops=1)
>> Recheck Cond:
>> (fdate_proccess = ((now())::date - 90))
>> -> Bitmap Index
>> Scan on ttransaction_det_idx3 (cost=0.00..733.75 rows=44887 width=0) (actual
>> time=6.046..6.046 rows=34075 loops=1)
>> Index Cond:
>> (fdate_proccess = ((now())::date - 90))
>> -> Hash
>> (cost=73109.94..73109.94 rows=51955 width=8) (actual time=59.944..59.944
>> rows=34649 loops=1)
>> -> Bitmap Heap
>> Scan on ttransaction_head a (cost=1459.53..73109.94 rows=51955 width=8)
>> (actual
>> time=6.694..35.741 rows=34649 loops=1)
>> Recheck Cond:
>> (fdate_proccess = ((now())::date - 90))
>> -> Bitmap
>> Index Scan on ttransaction_head_idx (cost=0.00..1446.54 rows=51955 width=0)
>> (actual time=5.860..5.860 rows=34649 loops=1)
>> Index
>> Cond: (fdate_proccess = ((now())::date - 90))
>> Total runtime: 196.904
>> msEn este la busqueda es totalmente indexada pero tuve que agregar la fecha
>> en el detalle. El modelo que estoy usando es Header/Detalle. Mi tabla de
>> header 3.5 millones de records
>>
>> Ahora veamos como deberia ser por normalizacion:
>> SELECT
>>
>>
>> B.fid_disposition,
>>
>> COUNT(1)
>> FROM
>>
>>
>> aegon.ttransaction_head A,
>>
>> aegon.ttransaction_det B
>> WHERE
>> 1=1
>> AND
>> A.fdate_proccess=NOW()::DATE-90
>> AND
>> B.fsec_doc=A.fsec_doc
>> GROUP BY
>> B.fid_disposition
>>
>>
>> QUERY
>> PLAN
>> HashAggregate
>> (cost=494710.46..494710.62 rows=13 width=8) (actual time=30808.479..30808.498
>> rows=26 loops=1)
>> -> Hash Join
>> (cost=73937.37..493899.45 rows=162201 width=8) (actual
>> time=106.614..30780.621
>> rows=34075 loops=1)
>> Hash Cond: (b.fsec_doc =
>> a.fsec_doc)
>> -> Seq Scan on
>> ttransaction_det b (cost=0.00..274669.35 rows=9541235 width=12) (actual
>> time=0.010..21649.412 rows=9541235 loops=1)
>> -> Hash
>> (cost=73109.94..73109.94 rows=51955 width=4) (actual time=58.139..58.139
>> rows=34649 loops=1)
>> -> Bitmap Heap
>> Scan on ttransaction_head a (cost=1459.53..73109.94 rows=51955 width=4)
>> (actual
>> time=6.664..34.658 rows=34649 loops=1)
>> Recheck Cond:
>> (fdate_proccess = ((now())::date - 90))
>> -> Bitmap
>> Index Scan on ttransaction_head_idx (cost=0.00..1446.54 rows=51955 width=0)
>> (actual time=5.825..5.825 rows=34649 loops=1)
>> Index
>> Cond: (fdate_proccess = ((now())::date - 90))
>> Total runtime: 30808.604
>> ms
>> La pregunta porque el en la tabla
>> ttransaction_det usa seq si existe un indice para ser usado en el campo
>> B.fsec_doc y cuando agrego la fecha a esta tabla, como se muestra en el
>> query anterior, tuve que agregar la fecha a la tabla de detalle para lograr
>> esos tiempos, obviamente esto no es muy deseable pero funciona.
>>
>>
>>
>>
>>
>>
>> *-------------------------------------------------------*
>> *-Edwin Quijada
>> *-Developer DataBase
>> *-JQ Microsistemas
>> *-809-849-8087
>> * " Si deseas lograr cosas excepcionales debes de hacer cosas fuera de lo
>> comun"
>> *-------------------------------------------------------*
>
> Puedes probar esto:
>
> SELECT
> B.fid_disposition,
> COUNT(1)
> FROM
> aegon.ttransaction_head A left join aegon.ttransaction_det B
> on (B.fsec_doc=A.fsec_doc)
> WHERE
> A.fdate_proccess=NOW()::DATE-90
> GROUP BY
> B.fid_disposition
>
> Y pasar su explain...
> Un abrazo...
QUERY
PLAN
HashAggregate
(cost=1912625.95..1912626.11 rows=13 width=8) (actual time=70053.267..70053.288
rows=27 loops=1)
-> Merge Left
Join (cost=1893364.90..1911814.94 rows=162201 width=8) (actual
time=69865.481..70028.299 rows=34649 loops=1)
Merge Cond:
(a.fsec_doc = b.fsec_doc)
-> Sort
(cost=41212.03..41341.92 rows=51955 width=4) (actual time=85.736..126.155
rows=34649 loops=1)
Sort Key:
a.fsec_doc
Sort
Method: external merge Disk: 536kB
->
Bitmap Heap Scan on ttransaction_head a (cost=863.18..36519.67 rows=51955
width=4) (actual time=6.515..33.626 rows=34649
loops=1)
Recheck Cond: (fdate_proccess = ((now())::date -
90))
-> Bitmap Index Scan on ttransaction_head_idx (cost=0.00..850.19 rows=51955
width=0) (actual time=5.715..5.715 rows=34649
loops=1)
Index Cond: (fdate_proccess = ((now())::date - 90))
->
Materialize (cost=1821037.54..1940302.98 rows=9541235 width=12) (actual
time=53054.609..65731.956 rows=7197404 loops=1)
->
Sort (cost=1821037.54..1844890.63 rows=9541235 width=12) (actual
time=53054.599..59054.449 rows=7197404 loops=1)
Sort Key: b.fsec_doc
Sort Method: external merge Disk: 223744kB
-> Seq Scan on ttransaction_det b (cost=0.00..274669.35 rows=9541235
width=12) (actual time=0.011..23617.133 rows=9541235
loops=1)
Total runtime:
70156.264 ms
> --
> §~^Calabaza^~§ from Villa Elisa, Paraguay
> ----------------
> A hendu hína: artist - Track 05
> http://foxytunes.com/artist/artist/track/track+05
> --
> TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net
_________________________________________________________________
See how Windows® connects the people, information, and fun that are part of
your life
http://clk.atdmt.com/MRT/go/119463819/direct/01/--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podrá leerlo
