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

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=1width=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.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: 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=1width=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=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.108..0.245rows=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.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.71rows=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
withouttime zone) AND (timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone)) 
 Total runtime: 257383.922 ms


Thanks

M.