2008/11/10 Edwin Quijada <[EMAIL PROTECTED]>: > >> 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
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 -- TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net
