Re: SELECT muy LENTO

2008/11/10 Edwin Quijada <listas_quijada(at)hotmail(dot)com>:
>
>> 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

Uf, nada optimo, podrías enviar las setencias create table de tus tablas?

--
§~^Calabaza^~§ from Villa Elisa, Paraguay
----------------
A hendu hína: artist - Track 05
http://foxytunes.com/artist/artist/track/track+05