Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Saturación PostgreSQL

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(dot)ogiser(at)gmail(dot)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 <silvioq(at)gmail(dot)com> escribió:
>
> El 14 de julio de 2010 17:43, OgiSer Tamade <tamade(dot)ogiser(at)gmail(dot)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 <silvioq(at)gmail(dot)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
>>
>
>