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

----- Original Message -----
> From: "Robert Haas" <robertmhaas(at)gmail(dot)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 really don'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.79 rows=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.16 rows=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) (actual time=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.004 rows=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) (actual time=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.007 rows=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) (actual time=0.003..0.003 rows=1 loops=50)
Index Cond: (id_sms_messaggio = sms.id)
Total runtime: 4.112 ms

Regards,

Matteo