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