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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
