Re: partitioned table: differents plans, slow on some situations

2011/12/30 Matteo Sgalaberni <sgala(at)sgala(dot)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