pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpartitioned table: differents plans, slow on some situations
5+ messages / 3 participants
[nested] [flat]
* partitioned table: differents plans, slow on some situations
@ 2011-12-30 16:01 Matteo Sgalaberni <sgala@sgala.com>
0 siblings, 1 reply; 5+ messages in thread
From: Matteo Sgalaberni @ 2011-12-30 16:01 UTC (permalink / raw)
To: pgsql-performance
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=1 width=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.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: 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.98 rows=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.17 rows=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=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.032..0.122 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.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.71 rows=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 without time 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partitioned table: differents plans, slow on some situations
@ 2011-12-30 16:23 Marcin Mirosław <marcin@mejor.pl>
parent: Matteo Sgalaberni <sgala@sgala.com>
0 siblings, 1 reply; 5+ messages in thread
From: Marcin Mirosław @ 2011-12-30 16:23 UTC (permalink / raw)
To: pgsql-performance
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 customer id.
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partitioned table: differents plans, slow on some situations
@ 2011-12-30 16:35 Matteo Sgalaberni <sgala@sgala.com>
parent: Marcin Mirosław <marcin@mejor.pl>
0 siblings, 1 reply; 5+ messages in thread
From: Matteo Sgalaberni @ 2011-12-30 16:35 UTC (permalink / raw)
To: pgsql-performance; +Cc: Marcin Mirosław <marcin@mejor.pl>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partitioned table: differents plans, slow on some situations
@ 2012-01-11 01:53 Robert Haas <robertmhaas@gmail.com>
parent: Matteo Sgalaberni <sgala@sgala.com>
0 siblings, 0 replies; 5+ messages in thread
From: Robert Haas @ 2012-01-11 01:53 UTC (permalink / raw)
To: Matteo Sgalaberni <sgala@sgala.com>; +Cc: pgsql-performance@postgresql.org, Marcin Mirosław <marcin@mejor.pl>
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=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
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: partitioned table: differents plans, slow on some situations
@ 2012-01-11 11:57 Matteo Sgalaberni <sgala@sgala.com>
0 siblings, 0 replies; 5+ messages in thread
From: Matteo Sgalaberni @ 2012-01-11 11:57 UTC (permalink / raw)
To: 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 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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-01-11 11:57 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-12-30 16:01 partitioned table: differents plans, slow on some situations Matteo Sgalaberni <sgala@sgala.com>
2011-12-30 16:23 ` Marcin Mirosław <marcin@mejor.pl>
2011-12-30 16:35 ` Matteo Sgalaberni <sgala@sgala.com>
2012-01-11 01:53 ` Robert Haas <robertmhaas@gmail.com>
2012-01-11 11:57 Re: partitioned table: differents plans, slow on some situations Matteo Sgalaberni <sgala@sgala.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox