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

Hi,

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.

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;

PLAN:

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.02..943.11 rows=50 width=16) (actual time=0.616..83.103 rows=50 loops=1)
   ->  Nested Loop  (cost=0.02..107279143.34 rows=5687651 width=16) (actual time=0.615..83.045 rows=50 loops=1)
         Join Filter: (sms.id = dlr.id_sms_messaggio)
         ->  Merge Append  (cost=0.02..20289460.70 rows=5687651 width=16) (actual time=0.046..15.379 rows=5874 loops=1)
               Sort Key: dlr.timestamp_todeliver
               ->  Index Scan Backward using sms_messaggio_dlr_todeliver on sms_messaggio_dlr dlr  (cost=0.00..8.27 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)
                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
               ->  Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201003 on sms_messaggio_dlr_201003 dlr  (cost=0.00..12428664.98 rows=3502530 width=16) (actual time=0.018..0.018 rows=1 loops=1)
                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
               ->  Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201004 on sms_messaggio_dlr_201004 dlr  (cost=0.00..7756421.17 rows=2185120 width=16) (actual time=0.023..8.458 rows=5874 loops=1)
                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
         ->  Append  (cost=0.00..15.26 rows=3 width=8) (actual time=0.010..0.010 rows=0 loops=5874)
               ->  Index Scan using sms_messaggio_pkey1 on sms_messaggio sms  (cost=0.00..0.28 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=5874)
                     Index Cond: (id = dlr.id_sms_messaggio)
                     Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 13))
               ->  Index Scan using sms_messaggio_201003_pkey on sms_messaggio_201003 sms  (cost=0.00..7.54 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=5874)
                     Index Cond: (id = dlr.id_sms_messaggio)
                     Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 13))
               ->  Index Scan using sms_messaggio_201004_pkey on sms_messaggio_201004 sms  (cost=0.00..7.45 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=5874)
                     Index Cond: (id = dlr.id_sms_messaggio)
                     Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 13))
 Total runtime: 83.201 ms

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;

PLAN:

 Limit  (cost=0.02..78345.78 rows=50 width=54) (actual time=8852.661..269509.298 rows=50 loops=1)
   ->  Nested Loop  (cost=0.02..58256338.38 rows=37179 width=54) (actual time=8852.658..269509.225 rows=50 loops=1)
         Join Filter: (sms.id = dlr.id_sms_messaggio)
         ->  Merge Append  (cost=0.02..20289460.70 rows=5687651 width=54) (actual time=0.067..4016.421 rows=1568544 loops=1)
               Sort Key: dlr.timestamp_todeliver
               ->  Index Scan using sms_messaggio_dlr_todeliver on sms_messaggio_dlr dlr  (cost=0.00..8.27 rows=1 width=101) (actual time=0.005..0.005 rows=0 loops=1)
                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
               ->  Index Scan using sms_messaggio_dlr_timestamp_todeliver_201003 on sms_messaggio_dlr_201003 dlr  (cost=0.00..12428664.98 rows=3502530 width=54) (actual time=0.030..2405.200 rows=1568544 loops=1)
                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
               ->  Index Scan using sms_messaggio_dlr_timestamp_todeliver_201004 on sms_messaggio_dlr_201004 dlr  (cost=0.00..7756421.17 rows=2185120 width=55) (actual time=0.028..0.028 rows=1 loops=1)
                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
         ->  Materialize  (cost=0.00..1715.42 rows=445 width=8) (actual time=0.001..0.080 rows=161 loops=1568544)
               ->  Append  (cost=0.00..1713.20 rows=445 width=8) (actual time=0.034..0.337 rows=161 loops=1)
                     ->  Seq Scan on sms_messaggio sms  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
                           Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 7))
                     ->  Bitmap Heap Scan on sms_messaggio_201003 sms  (cost=6.85..1199.49 rows=313 width=8) (actual time=0.032..0.122 rows=94 loops=1)
                           Recheck Cond: (id_cliente = 7)
                           Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
                           ->  Bitmap Index Scan on sms_messaggio_id_cliente_201003  (cost=0.00..6.78 rows=313 width=0) (actual time=0.022..0.022 rows=94 loops=1)
                                 Index Cond: (id_cliente = 7)
                     ->  Index Scan using sms_messaggio_id_cliente_timestamp_201004 on sms_messaggio_201004 sms  (cost=0.00..513.71 rows=131 width=8) (actual time=0.016..0.072 rows=67 loops=1)
                           Index Cond: ((id_cliente = 7) AND (timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
 Total runtime: 269510.002 ms

I'm using pg 9.1

Can someone explain me why the planner do this?

Thanks

Matteo

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.

I'm sorry, I pasted the wrong ones, but the results are the same, here A and B again:

Query A

# EXPLAIN ANALYZE 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 PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.02..943.11 rows=50 width=16) (actual time=0.603..79.729 rows=50 loops=1)
-> Nested Loop (cost=0.02..107279143.34 rows=5687651 width=16) (actual time=0.601..79.670 rows=50 loops=1)
Join Filter: (sms.id = dlr.id_sms_messaggio)
-> Merge Append (cost=0.02..20289460.70 rows=5687651 width=16) (actual time=0.048..14.556 rows=5874 loops=1)
Sort Key: dlr.timestamp_todeliver
-> Index Scan Backward using sms_messaggio_dlr_todeliver on sms_messaggio_dlr dlr (cost=0.00..8.27 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
-> Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201003 on sms_messaggio_dlr_201003 dlr (cost=0.00..12428664.98 rows=3502530 width=16) (actual time=0.018..0.018 rows=1 loops=1)
Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
-> Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201004 on sms_messaggio_dlr_201004 dlr (cost=0.00..7756421.17 rows=2185120 width=16) (actual time=0.022..8.408 rows=5874 loops=1)
Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
-> Append (cost=0.00..15.26 rows=3 width=8) (actual time=0.010..0.010 rows=0 loops=5874)
-> Index Scan using sms_messaggio_pkey1 on sms_messaggio sms (cost=0.00..0.28 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=5874)
Index Cond: (id = dlr.id_sms_messaggio)
Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 13))
-> Index Scan using sms_messaggio_201003_pkey on sms_messaggio_201003 sms (cost=0.00..7.54 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=5874)
Index Cond: (id = dlr.id_sms_messaggio)
Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 13))
-> Index Scan using sms_messaggio_201004_pkey on sms_messaggio_201004 sms (cost=0.00..7.45 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=5874)
Index Cond: (id = dlr.id_sms_messaggio)
Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 13))
Total runtime: 79.821 ms
(22 rows)

Query B:
# EXPLAIN ANALYZE 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 = '7'
ORDER BY dlr.timestamp_todeliver DESC LIMIT 50;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.02..78345.66 rows=50 width=16) (actual time=183.547..257383.459 rows=50 loops=1)
-> Nested Loop (cost=0.02..58256245.44 rows=37179 width=16) (actual time=183.544..257383.379 rows=50 loops=1)
Join Filter: (sms.id = dlr.id_sms_messaggio)
-> Merge Append (cost=0.02..20289460.70 rows=5687651 width=16) (actual time=0.047..4040.930 rows=1490783 loops=1)
Sort Key: dlr.timestamp_todeliver
-> Index Scan Backward using sms_messaggio_dlr_todeliver on sms_messaggio_dlr dlr (cost=0.00..8.27 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
-> Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201003 on sms_messaggio_dlr_201003 dlr (cost=0.00..12428664.98 rows=3502530 width=16) (actual time=0.018..0.018 rows=1 loops=1)
Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
-> Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201004 on sms_messaggio_dlr_201004 dlr (cost=0.00..7756421.17 rows=2185120 width=16) (actual time=0.022..2511.283 rows=1490783 loops=1)
Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
-> Materialize (cost=0.00..1715.42 rows=445 width=8) (actual time=0.001..0.081 rows=161 loops=1490783)
-> Append (cost=0.00..1713.20 rows=445 width=8) (actual time=0.111..0.502 rows=161 loops=1)
-> Seq Scan on sms_messaggio sms (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 7))
-> Bitmap Heap Scan on sms_messaggio_201003 sms (cost=6.85..1199.49 rows=313 width=8) (actual time=0.108..0.245 rows=94 loops=1)
Recheck Cond: (id_cliente = 7)
Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on sms_messaggio_id_cliente_201003 (cost=0.00..6.78 rows=313 width=0) (actual time=0.083..0.083 rows=94 loops=1)
Index Cond: (id_cliente = 7)
-> Index Scan using sms_messaggio_id_cliente_timestamp_201004 on sms_messaggio_201004 sms (cost=0.00..513.71 rows=131 width=8) (actual time=0.059..0.113 rows=67 loops=1)
Index Cond: ((id_cliente = 7) AND (timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
Total runtime: 257383.922 ms

Thanks

M.

2011/12/30 Matteo Sgalaberni <sgala(at)sgala(dot)com>:
> I'm sorry, I pasted the wrong ones, but the results are the same, here A and B again:
>
> Query A
>
> # EXPLAIN  ANALYZE 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 PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=0.02..943.11 rows=50 width=16) (actual time=0.603..79.729 rows=50 loops=1)
>   ->  Nested Loop  (cost=0.02..107279143.34 rows=5687651 width=16) (actual time=0.601..79.670 rows=50 loops=1)
>         Join Filter: (sms.id = dlr.id_sms_messaggio)
>         ->  Merge Append  (cost=0.02..20289460.70 rows=5687651 width=16) (actual time=0.048..14.556 rows=5874 loops=1)
>               Sort Key: dlr.timestamp_todeliver
>               ->  Index Scan Backward using sms_messaggio_dlr_todeliver on sms_messaggio_dlr dlr  (cost=0.00..8.27 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)
>                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
>               ->  Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201003 on sms_messaggio_dlr_201003 dlr  (cost=0.00..12428664.98 rows=3502530 width=16) (actual time=0.018..0.018 rows=1 loops=1)
>                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
>               ->  Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201004 on sms_messaggio_dlr_201004 dlr  (cost=0.00..7756421.17 rows=2185120 width=16) (actual time=0.022..8.408 rows=5874 loops=1)
>                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
>         ->  Append  (cost=0.00..15.26 rows=3 width=8) (actual time=0.010..0.010 rows=0 loops=5874)
>               ->  Index Scan using sms_messaggio_pkey1 on sms_messaggio sms  (cost=0.00..0.28 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=5874)
>                     Index Cond: (id = dlr.id_sms_messaggio)
>                     Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 13))
>               ->  Index Scan using sms_messaggio_201003_pkey on sms_messaggio_201003 sms  (cost=0.00..7.54 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=5874)
>                     Index Cond: (id = dlr.id_sms_messaggio)
>                     Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 13))
>               ->  Index Scan using sms_messaggio_201004_pkey on sms_messaggio_201004 sms  (cost=0.00..7.45 rows=1 width=8) (actual time=0.004..0.004 rows=0 loops=5874)
>                     Index Cond: (id = dlr.id_sms_messaggio)
>                     Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 13))
>  Total runtime: 79.821 ms
> (22 rows)
>
> Query B:
> # EXPLAIN  ANALYZE 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 = '7'
>                      ORDER BY dlr.timestamp_todeliver DESC LIMIT 50;
>                                                                                                         QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Limit  (cost=0.02..78345.66 rows=50 width=16) (actual time=183.547..257383.459 rows=50 loops=1)
>   ->  Nested Loop  (cost=0.02..58256245.44 rows=37179 width=16) (actual time=183.544..257383.379 rows=50 loops=1)
>         Join Filter: (sms.id = dlr.id_sms_messaggio)
>         ->  Merge Append  (cost=0.02..20289460.70 rows=5687651 width=16) (actual time=0.047..4040.930 rows=1490783 loops=1)
>               Sort Key: dlr.timestamp_todeliver
>               ->  Index Scan Backward using sms_messaggio_dlr_todeliver on sms_messaggio_dlr dlr  (cost=0.00..8.27 rows=1 width=16) (actual time=0.005..0.005 rows=0 loops=1)
>                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
>               ->  Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201003 on sms_messaggio_dlr_201003 dlr  (cost=0.00..12428664.98 rows=3502530 width=16) (actual time=0.018..0.018 rows=1 loops=1)
>                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
>               ->  Index Scan Backward using sms_messaggio_dlr_timestamp_todeliver_201004 on sms_messaggio_dlr_201004 dlr  (cost=0.00..7756421.17 rows=2185120 width=16) (actual time=0.022..2511.283 rows=1490783 loops=1)
>                     Index Cond: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
>         ->  Materialize  (cost=0.00..1715.42 rows=445 width=8) (actual time=0.001..0.081 rows=161 loops=1490783)
>               ->  Append  (cost=0.00..1713.20 rows=445 width=8) (actual time=0.111..0.502 rows=161 loops=1)
>                     ->  Seq Scan on sms_messaggio sms  (cost=0.00..0.00 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=1)
>                           Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone) AND (id_cliente = 7))
>                     ->  Bitmap Heap Scan on sms_messaggio_201003 sms  (cost=6.85..1199.49 rows=313 width=8) (actual time=0.108..0.245 rows=94 loops=1)
>                           Recheck Cond: (id_cliente = 7)
>                           Filter: ((timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
>                           ->  Bitmap Index Scan on sms_messaggio_id_cliente_201003  (cost=0.00..6.78 rows=313 width=0) (actual time=0.083..0.083 rows=94 loops=1)
>                                 Index Cond: (id_cliente = 7)
>                     ->  Index Scan using sms_messaggio_id_cliente_timestamp_201004 on sms_messaggio_201004 sms  (cost=0.00..513.71 rows=131 width=8) (actual time=0.059..0.113 rows=67 loops=1)
>                           Index Cond: ((id_cliente = 7) AND (timestamp_todeliver >= '2010-03-01 00:00:00'::timestamp without time zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
>  Total runtime: 257383.922 ms

Hmm. In the first (good) plan, the planner is using a parameterized
nestloop. So for each row it finds in dlr, it looks up
dlr.id_sms_messaggio and passes that down to the index scans, which
then pull out just the rows where sms.id takes that specific value.
In the second (bad) plan, the planner is using an unparameterized
nestloop: it's fetching all 445 rows that match the remaining criteria
on sms_messagio (i.e. date and id_cliente) and then repeatedly
rescanning the output of that calculation. My guess is that the
planner figures that repeated index scans are going to cause too much
I/O, and that caching the results is better; you might want to check
your values for random_page_cost, seq_page_cost, and
effective_cache_size.

That having been said, if the planner doesn't like the idea of
repeatedly index-scanning, why not use a hash join instead of a nested
loop? That seems likely to be a whole lot faster for the 445 rows the
planner is estimating. Can you show us all of your non-default
configuration settings?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company