----- Original Message -----
> From: "Robert Haas" <[email protected]>
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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
