partitioned table: differents plans, slow on some situations - Mailing list pgsql-performance

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=1width=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.001rows=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.98rows=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.17rows=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=0loops=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.122rows=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)
(actualtime=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.71rows=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
withouttime 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