Re: partitioned table: differents plans, slow on some situations

W dniu 30.12.2011 17:01, Matteo Sgalaberni pisze:
> Hi,

Hello,

> I have a two tables that are partitioned by month.
>
> I have different results for the same query (query A/query B), the only thing that differ from A and B is the customer id.

Not only:

> Query A:
>
> SELECT sms.id AS id_sms
>
> FROM
> sms_messaggio AS sms,
> sms_messaggio_dlr AS dlr
> WHERE sms.id = dlr.id_sms_messaggio
> AND sms.timestamp_todeliver >= '1/3/2010'::timestamp
> AND sms.timestamp_todeliver < '30/4/2010'::timestamp
> AND dlr.timestamp_todeliver >= '1/3/2010'::timestamp
> AND dlr.timestamp_todeliver < '30/4/2010'::timestamp
> AND sms.id_cliente = '13'
> ORDER BY dlr.timestamp_todeliver DESC LIMIT 50;
^^^^^^^

> Query B:
> EXPLAIN ANALYZE SELECT sms.id AS id_sms,
> dlr.msisdn,
> to_char(dlr.timestamp_stato,'DD/MM/YYYY HH24:MI:SS') AS timestamp_stato,
> dlr.stato,
> dlr.id AS id_dlr,
> dlr.numero_pdu,
> dlr.costo_cli
> FROM
> sms_messaggio AS sms,
> sms_messaggio_dlr AS dlr
> WHERE sms.id = dlr.id_sms_messaggio
> AND sms.timestamp_todeliver >= '1/3/2010'::timestamp
> AND sms.timestamp_todeliver < '30/4/2010'::timestamp
> AND dlr.timestamp_todeliver >= '1/3/2010'::timestamp
> AND dlr.timestamp_todeliver < '30/4/2010'::timestamp
> AND sms.id_cliente = '7'
> ORDER BY dlr.timestamp_todeliver ASC LIMIT 50;
^^^^^
> I'm using pg 9.1
>
> Can someone explain me why the planner do this?

Those queries are diffrent.
Regards.