Nombre Definición Restricciones ¿Tabla Ordenada? Acciones Comentario p_phonestatus_pkey CREATE UNIQUE INDEX p_phonestatus_pkey ON p_phonestatus USING btree (phoneid, sequenceid) Llave primaria No Ordenar tabla<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_cluster_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=dialapplet_predictivedialer_phonestatus_pkey&;> Reindexar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=reindex&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=dialapplet_predictivedialer_phonestatus_pkey&;>
insertdate_index CREATE INDEX insertdate_index ON p_phonestatus USING btree (insertdate) No Ordenar tabla<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_cluster_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=insertdate_index&;> Reindexar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=reindex&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=insertdate_index&;> Eliminar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_drop_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=insertdate_index&;> phonestatus_assignedto_index CREATE INDEX phonestatus_assignedto_index ON p_phonestatus USING hash (assignedto) No Ordenar tabla<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_cluster_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_assignedto_index&;> Reindexar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=reindex&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_assignedto_index&;> Eliminar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_drop_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_assignedto_index&;> phonestatus_campaignid CREATE INDEX phonestatus_campaignid ON p_phonestatus USING btree (campaignid) No Ordenar tabla<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_cluster_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_campaignid&;> Reindexar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=reindex&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_campaignid&;> Eliminar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_drop_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_campaignid&;> phonestatus_status_index CREATE INDEX phonestatus_status_index ON p_phonestatus USING btree (status) No Ordenar tabla<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_cluster_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_status_index&;> Reindexar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=reindex&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_status_index&;> Eliminar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_drop_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_status_index&;> phonestatus_talkseconds_index CREATE INDEX phonestatus_talkseconds_index ON p__phonestatus USING btree (talkseconds) No Ordenar tabla<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_cluster_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_talkseconds_index&;> Reindexar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=reindex&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_talkseconds_index&;> Eliminar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_drop_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonestatus&index=phonestatus_talkseconds_index&;> Nombre Definición Restricciones ¿Tabla Ordenada? Acciones Comentario campaigind CREATE INDEX campaigind ON p_phonenumbers USING btree (campaignid) No Ordenar tabla<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_cluster_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonenumbers&index=campaigind&;> Reindexar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=reindex&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonenumbers&index=campaigind&;> Eliminar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_drop_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonenumbers&index=campaigind&;> p_phonenumbers_pkey CREATE UNIQUE INDEX p_phonenumbers_pkey ON p_phonenumbers USING btree (phoneid) Llave primaria No Ordenar tabla<http://127.0.0.1:8888/phppgadmin/indexes.php?action=confirm_cluster_index&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonenumbers&index=dialapplet_predictivedialer_phonenumbers_pkey&;> Reindexar<http://127.0.0.1:8888/phppgadmin/indexes.php?action=reindex&server=localhost%3A5432%3Aallow&database=dialapplet&schema=public&table=dialapplet_predictivedialer_phonenumbers&index=dialapplet_predictivedialer_phonenumbers_pkey&;> phonenumbers_campaignid_index CREATE INDEX phonenumbers_campaignid_index ON p__phonenumbers USING hash (campaignid) No El 14 de julio de 2010 23:25, OgiSer Tamade <tamade.ogi...@gmail.com>escribió: > > SELECT S.phoneid, S.sequenceid > FROM d_campaigns C, d_phonestatus S > WHERE C.campaignid IN (17,16,15,14,13) AND C.campaignid=S.campaignid > AND > S.sequenceid=( > > SELECT MAX(S2.sequenceid) FROM d_phonestatus S2 WHERE > S2.phoneid=S.phoneid ) > > AND (S.assignedto IS NULL OR (S.assignedto<>'A436MA' AND > (S.status='rejected' OR > S.status='error'))) > AND (S.callbackdate <= NOW() OR S.callbackdate IS > NULL) > ORDER BY S.callbackdate, C.priority DESC, S.phoneid LIMIT 1 > > > > > El 14 de julio de 2010 23:05, Silvio Quadri <silv...@gmail.com> escribió: > > El 14 de julio de 2010 17:43, OgiSer Tamade <tamade.ogi...@gmail.com> >> escribió: >> > >> > Hola, >> > >> > adjunto el explain, se hace 2 vacuum al día. El servidor tiene 4 nucleos >> vendor_id, hoy ejemplo me ha pasado con 95 usuarios, si bajamos a 75 >> funciona correctamente. Vuelvo a adjuntar el postgree.conf >> > >> > : GenuineIntel >> > cpu family : 6 >> > model : 23 >> > model name : Intel(R) Xeon(R) CPU X3330 @ 2.66GHz >> > stepping : 10 >> > cpu MHz : 1998.000 >> > cache size : 3072 KB >> > >> > >> > Limit (cost=702.42..702.43 rows=1 width=121) >> > >> > -> Sort (cost=702.42..702.43 rows=1 width=121) >> > >> > Sort Key: s.callbackdate, c.priority, p.phoneid >> > >> > -> Nested Loop (cost=321.26..702.41 rows=1 width=121) >> > >> > Join Filter: ("inner".campaignid = "outer".campaignid) >> > >> > -> Nested Loop (cost=321.26..701.23 rows=1 width=97) >> > >> > -> Bitmap Heap Scan on phonestatus s >> (cost=321.26..695.25 rows=1 width=16) >> > >> > Recheck Cond: ((assignedto = 'A527MA'::text) >> AND (status = 'assigned'::text)) >> > >> > Filter: (((callbackdate <= now()) OR >> (callbackdate IS NULL)) AND (sequenceid = (subplan))) >> > >> > -> BitmapAnd (cost=321.26..321.26 rows=42 >> width=0) >> > >> > -> Bitmap Index Scan on >> phonestatus_assignedto_index (cost=0.00..73.66 rows=2473 width=0) >> > >> > Index Cond: (assignedto = >> 'A527MA'::text) >> > >> > -> Bitmap Index Scan on >> phonestatus_status_index (cost=0.00..247.36 rows=7816 width=0) >> > >> > Index Cond: (status = >> 'assigned'::text) >> > >> > SubPlan >> > >> > -> Result (cost=5.01..5.02 rows=1 width=0) >> > >> > InitPlan >> > >> > -> Limit (cost=0.00..5.01 rows=1 >> width=4) >> > >> > -> Index Scan Backward using >> phonestatus_pkey on phonestatus s2 (cost=0.00..10.03 rows=2 width=4) >> > >> > Index Cond: (phoneid = >> $0) >> > >> > Filter: (sequenceid IS >> NOT NULL) >> > >> > -> Index Scan using phonenumbers_pkey on >> phonenumbers p (cost=0.00..5.97 rows=1 width=85) >> > >> > Index Cond: (p.phoneid = "outer".phoneid) >> > >> > Filter: ((campaignid = 31) OR (campaignid = >> 30) OR (campaignid = 29) OR (campaignid = 28) OR (campaignid = 26)) >> > >> > -> Seq Scan on campaigns c (cost=0.00..1.08 rows=8 >> width=32) >> > >> > El 13 de julio de 2010 16:43, Silvio Quadri <silv...@gmail.com> >> escribió: >> >> Estaría bien ver la consulta. >> >> Algunos tips. >> 1. ¿Probaste con un índice assignedto / status? >> 2. El status convendría que lo pases a numérico. Si no lo usás para >> otra cosa, en esta consulta ese índice está de más y podrías >> eliminarlo. >> 3. ¿Callbackdate puede ser futuro? >> >> Silvio >> > >
