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=1 width=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.001 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_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=1 width=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=0 loops=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.245 rows=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) (actual
time=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.71 rows=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 without time zone) AND
(timestamp_todeliver < '2010-04-30 00:00:00'::timestamp without time zone))
Total runtime: 257383.922 ms
Thanks
M.
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
