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

Algunos tips.
1. ¿Probaste con un índice assignedto / status?
Si, no obstante te he enviado todos los indices.

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.

lo utilizo en otra parte del codigo.

3. ¿Callbackdate puede ser futuro?
si puede ser futuro.

El 14 de julio de 2010 23:39, OgiSer Tamade <tamade(dot)ogiser(at)gmail(dot)com>escribió:

> Limit (cost=557838.74..557838.74 rows=1 width=20) (actual time=0.076..0.076 rows=0 loops=1)
>
> -> Sort (cost=557838.74..557838.95 rows=86 width=20) (actual time=0.075..0.075 rows=0 loops=1)
>
> Sort Key: s.callbackdate, c.priority, s.phoneid
>
> -> Nested Loop (cost=1058.59..557835.97 rows=86 width=20) (actual time=0.017..0.017 rows=0 loops=1)
>
> -> Seq Scan on p_campaigns c (cost=0.00..1.34 rows=4 width=8) (actual time=0.017..0.017 rows=0 loops=1)
>
> Filter: ((campaignid = 17) OR (campaignid = 16) OR (campaignid = 15) OR (campaignid = 14) OR (campaignid = 13))
>
> -> Bitmap Heap Scan on p_phonestatus s (cost=1058.59..139454.62 rows=323 width=20) (never executed)
>
> Recheck Cond: ("outer".campaignid = s.campaignid)
>
> Filter: (((assignedto IS NULL) OR ((assignedto <> 'A436MA'::text) AND ((status = 'rejected'::text) OR (status = 'error'::text)))) AND ((callbackdate <= now()) OR (callbackdate IS NULL)) AND (sequenceid = (subplan)))
>
> -> Bitmap Index Scan on phonestatus_campaignid (cost=0.00..1058.59 rows=36740 width=0) (never executed)
>
> Index Cond: ("outer".campaignid = s.campaignid)
>
> SubPlan
>
> -> Result (cost=3.45..3.46 rows=1 width=0) (never executed)
>
> InitPlan
>
> -> Limit (cost=0.00..3.45 rows=1 width=4) (never executed)
>
> -> Index Scan Backward using p_phonestatus_pkey on p_phonestatus s2 (cost=0.00..6.90 rows=2 width=4) (never executed)
>
> Index Cond: (phoneid = $0)
>
> Filter: (sequenceid IS NOT NULL)
>
>
>
> El 14 de julio de 2010 23:32, Alvaro Herrera <alvherre(at)commandprompt(dot)com>escribió:
>
> Excerpts from OgiSer Tamade's message of mié jul 14 17:25:53 -0400 2010:
>> > 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
>>
>> Esta no es la misma consulta de la que mostraste el EXPLAIN.
>>
>
>