RE: SELECT muy LENTO

> Date: Mon, 10 Nov 2008 11:36:49 -0400
> From: calalinux(at)gmail(dot)com
> To: pgsql-es-ayuda(at)postgresql(dot)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/