Thread: partitioned table: differents plans, slow on some situations
Hi, I have a two tables that are partitioned by month. I have different results for the same query (query A/query B), the only thing that differ from A and B is the customer id. Query A: 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; PLAN: ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.02..943.11 rows=50 width=16) (actual time=0.616..83.103 rows=50 loops=1) -> Nested Loop (cost=0.02..107279143.34 rows=5687651 width=16) (actual time=0.615..83.045 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.046..15.379 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.004..0.004 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.023..8.458 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: 83.201 ms Query B: EXPLAIN ANALYZE SELECT sms.id AS id_sms, dlr.msisdn, to_char(dlr.timestamp_stato,'DD/MM/YYYY HH24:MI:SS') AS timestamp_stato, dlr.stato, dlr.id AS id_dlr, dlr.numero_pdu, dlr.costo_cli 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 ASC LIMIT 50; PLAN: Limit (cost=0.02..78345.78 rows=50 width=54) (actual time=8852.661..269509.298 rows=50 loops=1) -> Nested Loop (cost=0.02..58256338.38 rows=37179 width=54) (actual time=8852.658..269509.225 rows=50 loops=1) Join Filter: (sms.id = dlr.id_sms_messaggio) -> Merge Append (cost=0.02..20289460.70 rows=5687651 width=54) (actual time=0.067..4016.421 rows=1568544 loops=1) Sort Key: dlr.timestamp_todeliver -> Index Scan using sms_messaggio_dlr_todeliver on sms_messaggio_dlr dlr (cost=0.00..8.27 rows=1 width=101)(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 using sms_messaggio_dlr_timestamp_todeliver_201003 on sms_messaggio_dlr_201003 dlr (cost=0.00..12428664.98rows=3502530 width=54) (actual time=0.030..2405.200 rows=1568544 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 using sms_messaggio_dlr_timestamp_todeliver_201004 on sms_messaggio_dlr_201004 dlr (cost=0.00..7756421.17rows=2185120 width=55) (actual time=0.028..0.028 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)) -> Materialize (cost=0.00..1715.42 rows=445 width=8) (actual time=0.001..0.080 rows=161 loops=1568544) -> Append (cost=0.00..1713.20 rows=445 width=8) (actual time=0.034..0.337 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.032..0.122rows=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.022..0.022 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.016..0.072 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: 269510.002 ms I'm using pg 9.1 Can someone explain me why the planner do this? Thanks Matteo
W dniu 30.12.2011 17:01, Matteo Sgalaberni pisze:
> Hi,
Hello,
> I have a two tables that are partitioned by month.
>
> I have different results for the same query (query A/query B), the only thing that differ from A and B is the
customerid.
Not only:
> Query A:
>
> 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 B:
> EXPLAIN ANALYZE SELECT sms.id AS id_sms,
> dlr.msisdn,
> to_char(dlr.timestamp_stato,'DD/MM/YYYY HH24:MI:SS') AS timestamp_stato,
> dlr.stato,
> dlr.id AS id_dlr,
> dlr.numero_pdu,
> dlr.costo_cli
> 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 ASC LIMIT 50;
^^^^^
> I'm using pg 9.1
>
> Can someone explain me why the planner do this?
Those queries are diffrent.
Regards.
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.
2011/12/30 Matteo Sgalaberni <sgala@sgala.com>: > 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)(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.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 Hmm. In the first (good) plan, the planner is using a parameterized nestloop. So for each row it finds in dlr, it looks up dlr.id_sms_messaggio and passes that down to the index scans, which then pull out just the rows where sms.id takes that specific value. In the second (bad) plan, the planner is using an unparameterized nestloop: it's fetching all 445 rows that match the remaining criteria on sms_messagio (i.e. date and id_cliente) and then repeatedly rescanning the output of that calculation. My guess is that the planner figures that repeated index scans are going to cause too much I/O, and that caching the results is better; you might want to check your values for random_page_cost, seq_page_cost, and effective_cache_size. That having been said, if the planner doesn't like the idea of repeatedly index-scanning, why not use a hash join instead of a nested loop? That seems likely to be a whole lot faster for the 445 rows the planner is estimating. Can you show us all of your non-default configuration settings? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
----- 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