Skip site navigation (1) Skip section navigation (2)

Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Re: [pgsql

From: OgiSer Tamade <tamade(dot)ogiser(at)gmail(dot)com>
To: pgsql-es-ayuda(at)postgresql(dot)org
Subject: Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Re: [pgsql
Date: 2010-07-14 21:33:42
Message-ID: AANLkTikvpupjrpHsuITG0yX6op08brH9p69e59zGtSvo@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-es-ayuda
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
>>
>
>

In response to

pgsql-es-ayuda by date

Next:From: OgiSer TamadeDate: 2010-07-14 21:39:16
Subject: Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Re: [pgsql
Previous:From: Alvaro HerreraDate: 2010-07-14 21:32:06
Subject: Re: Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Re: [pgsql-es-ayuda] Saturación PostgreSQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group