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

----- Original Message -----
> From: "Robert Haas" <robertmhaas@gmail.com>

Hi Robert,

I solved the problem by modifying the query:

before:
                   ORDER BY dlr.timestamp_todeliver DESC LIMIT

after:
                   ORDER BY sms.timestamp_todeliver DESC LIMIT

modifying this, the planner changed and computed the result in few ms (500ms before caching, 5ms after caching)...I
reallydon't understand why but is fine... 

                                                                                                QUERY PLAN
                              

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.02..2196.62 rows=50 width=16) (actual time=0.423..4.010 rows=50 loops=1)
   ->  Nested Loop  (cost=0.02..250390629.32 rows=5699521 width=16) (actual time=0.422..3.954 rows=50 loops=1)
         Join Filter: (sms.id = dlr.id_sms_messaggio)
         ->  Merge Append  (cost=0.02..11758801.28 rows=470529 width=16) (actual time=0.384..2.977 rows=50 loops=1)
               Sort Key: sms.timestamp_todeliver
               ->  Index Scan Backward using sms_messaggio_todeliver on sms_messaggio sms  (cost=0.00..8.27 rows=1
width=16)(actual time=0.006..0.006 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)) 
                     Filter: (id_cliente = 13)
               ->  Index Scan Backward using sms_messaggio_timestamp_todeliver_201003 on sms_messaggio_201003 sms
(cost=0.00..7645805.79rows=273298 width=16) (actual time=0.313..0.313 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)) 
                     Filter: (id_cliente = 13)
               ->  Index Scan Backward using sms_messaggio_timestamp_todeliver_201004 on sms_messaggio_201004 sms
(cost=0.00..4104353.16rows=197230 width=16) (actual time=0.062..2.600 rows=50 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)) 
                     Filter: (id_cliente = 13)
         ->  Append  (cost=0.00..505.46 rows=136 width=8) (actual time=0.016..0.017 rows=1 loops=50)
               ->  Index Scan using sms_messaggio_dlr_id_sms on sms_messaggio_dlr dlr  (cost=0.00..0.27 rows=1 width=8)
(actualtime=0.001..0.001 rows=0 loops=50) 
                     Index Cond: (id_sms_messaggio = sms.id)
                     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 Heap Scan on sms_messaggio_dlr_201003 dlr  (cost=4.89..274.56 rows=73 width=8) (actual
time=0.004..0.004rows=0 loops=50) 
                     Recheck Cond: (id_sms_messaggio = sms.id)
                     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_dlr_id_sms_201003  (cost=0.00..4.88 rows=73 width=0)
(actualtime=0.003..0.003 rows=0 loops=50) 
                           Index Cond: (id_sms_messaggio = sms.id)
               ->  Bitmap Heap Scan on sms_messaggio_dlr_201004 dlr  (cost=4.69..230.62 rows=62 width=8) (actual
time=0.006..0.007rows=1 loops=50) 
                     Recheck Cond: (id_sms_messaggio = sms.id)
                     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_dlr_id_sms_201004  (cost=0.00..4.68 rows=62 width=0)
(actualtime=0.003..0.003 rows=1 loops=50) 
                           Index Cond: (id_sms_messaggio = sms.id)
 Total runtime: 4.112 ms

Regards,

Matteo