Re: Join the master table with other table is very slow (partitioning)

Hi Rumman,

I think it works. Please see the following result. Thanks.

dailyest=# explain select * from par_est e where e.date BETWEEN
'2012-07-08' and '2012-07-10'
;
QUERY PLAN

--------------------------------------------------------------------------------------------------------
Result (cost=0.00..662886.68 rows=32485781 width=16)
-> Append (cost=0.00..662886.68 rows=32485781 width=16)
-> Seq Scan on par_est e (cost=0.00..0.00 rows=1 width=16)
Filter: ((date >= '2012-07-08'::date) AND (date <=
'2012-07-10'::date))
-> Seq Scan on par_est_2012_07 e (cost=0.00..0.00 rows=1
width=16)
Filter: ((date >= '2012-07-08'::date) AND (date <=
'2012-07-10'::date))
-> Seq Scan on par_est_2012_07_08 e (cost=0.00..220695.53
rows=10815502 width=16)
Filter: ((date >= '2012-07-08'::date) AND (date <=
'2012-07-10'::date))
-> Seq Scan on par_est_2012_07_09 e (cost=0.00..220942.20
rows=10827613 width=16)
Filter: ((date >= '2012-07-08'::date) AND (date <=
'2012-07-10'::date))
-> Seq Scan on par_est_2012_07_10 e (cost=0.00..221248.96
rows=10842664 width=16)
Filter: ((date >= '2012-07-08'::date) AND (date <=
'2012-07-10'::date))
(12 rows)

On Fri, Mar 15, 2013 at 11:12 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:

>
>
> On Fri, Mar 15, 2013 at 11:09 AM, Ao Jianwang <aojw2008(at)gmail(dot)com> wrote:
>
>> Hi Rumman,
>>
>> Thanks for your response. I follow the guide to build the partition. The
>> settings should be good. See the following result. Any insight? thanks.
>>
>> dailyest=# select version();
>> version
>>
>>
>> ------------------------------------------------------------------------------------------------------------
>> PostgreSQL 9.2.3 on x86_64-unknown-linux-gnu, compiled by gcc
>> (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit
>> (1 row)
>>
>> dailyest=# show constraint_exclusion;
>> constraint_exclusion
>> ----------------------
>> on
>> (1 row)
>>
>> On Fri, Mar 15, 2013 at 11:04 PM, AI Rumman <rummandba(at)gmail(dot)com> wrote:
>>
>>> Which version of Postgresql are you using?
>>> Have you set constraint_exclusion to parition?
>>>
>>>
>>> On Fri, Mar 15, 2013 at 11:02 AM, Ao Jianwang <aojw2008(at)gmail(dot)com>wrote:
>>>
>>>> Hi Experts,
>>>>
>>>> I found if we join the master table with other small table, then the
>>>> running time is slow. While, if we join each child table with the small
>>>> table, then it's very fast. Any comments and suggestions are greatly
>>>> appreciated.
>>>>
>>>> *For example, par_list table is small(about 50k rows), while par_est
>>>> is very large, for each day it's about 400MB. Therefore, we partition it by
>>>> day. However, the query plan for joining the master table with par_list is
>>>> bad, so the running time is slow. The good plan should be join each
>>>> partition table with par_list separately, then aggregate the result
>>>> together. *
>>>> *
>>>> *
>>>> *1. Join the master table with a small table. It's slow.*
>>>> dailyest=# explain (analyze on, buffers on)
>>>> dailyest-# SELECT e.date, max(e.estimate)
>>>> dailyest-# FROM
>>>> dailyest-# par_list l,
>>>> dailyest-# par_est e
>>>> dailyest-# WHERE
>>>> dailyest-# l.id = e.list_id and
>>>> dailyest-# e.date BETWEEN '2012-07-08' and '2012-07-10'
>>>> and
>>>> dailyest-# l.fid = 1 and
>>>> dailyest-# l.sid = 143441 and
>>>> dailyest-# l.cid in (36, 39, 6000) and
>>>> dailyest-# e.aid = 333710667
>>>> dailyest-# GROUP BY e.date
>>>> dailyest-# ORDER BY e.date;
>>>>
>>>> -----------------------
>>>> GroupAggregate (cost=745326.86..745326.88 rows=1 width=8) (actual
>>>> time=6281.364..6281.366 rows=3 loops=1)
>>>> Buffers: shared hit=3 read=175869
>>>> -> Sort (cost=745326.86..745326.86 rows=1 width=8) (actual
>>>> time=6281.358..6281.358 rows=6 loops=1)
>>>> Sort Key: e.date
>>>> Sort Method: quicksort Memory: 25kB
>>>> Buffers: shared hit=3 read=175869
>>>> -> Nested Loop (cost=0.00..745326.85 rows=1 width=8) (actual
>>>> time=1228.493..6281.349 rows=6 loops=1)
>>>> Join Filter: (l.id = e.list_id)
>>>> Rows Removed by Join Filter: 4040
>>>> Buffers: shared hit=3 read=175869
>>>> -> Seq Scan on par_list l (cost=0.00..1213.10 rows=2
>>>> width=4) (actual time=0.010..38.272 rows=2 loops=1)
>>>> Filter: ((fid = 1) AND (sid = 143441) AND (cid =
>>>> ANY ('{36,39,6000}'::integer[])))
>>>> Rows Removed by Filter: 50190
>>>> Buffers: shared hit=3 read=269
>>>> -> Materialize (cost=0.00..744102.56 rows=407
>>>> width=12) (actual time=9.707..3121.053 rows=2023 loops=2)
>>>> Buffers: shared read=175600
>>>> -> Append (cost=0.00..744100.52 rows=407
>>>> width=12) (actual time=19.410..6240.044 rows=2023 loops=1)
>>>> Buffers: shared read=175600
>>>> -> Seq Scan on par_est e (cost=0.00..0.00
>>>> rows=1 width=12) (actual time=0.001..0.001 rows=0 loops=1)
>>>> Filter: ((date >= '2012-07-08'::date)
>>>> AND (date <= '2012-07-10'::date) AND (aid = 333710667))
>>>> -> Seq Scan on par_est_2012_07 e
>>>> (cost=0.00..0.00 rows=1 width=12) (actual time=0.000..0.000 rows=0 loops=1)
>>>> Filter: ((date >= '2012-07-08'::date)
>>>> AND (date <= '2012-07-10'::date) AND (aid = 333710667))
>>>> -> Seq Scan on par_est_2012_07_08 e
>>>> (cost=0.00..247736.09 rows=135 width=12) (actual time=19.408..2088.627
>>>> rows=674 loops=1)
>>>> Filter: ((date >= '2012-07-08'::date)
>>>> AND (date <= '2012-07-10'::date) AND (aid = 333710667))
>>>> Rows Removed by Filter: 10814878
>>>> Buffers: shared read=58463
>>>> -> Seq Scan on par_est_2012_07_09 e
>>>> (cost=0.00..248008.81 rows=137 width=12) (actual time=6.390..1963.238
>>>> rows=676 loops=1)
>>>> Filter: ((date >= '2012-07-08'::date)
>>>> AND (date <= '2012-07-10'::date) AND (aid = 333710667))
>>>> Rows Removed by Filter: 10826866
>>>> Buffers: shared read=58528
>>>> -> Seq Scan on par_est_2012_07_10 e
>>>> (cost=0.00..248355.62 rows=133 width=12) (actual time=15.135..2187.312
>>>> rows=673 loops=1)
>>>> Filter: ((date >= '2012-07-08'::date)
>>>> AND (date <= '2012-07-10'::date) AND (aid = 333710667))
>>>> Rows Removed by Filter: 10841989
>>>> Buffers: shared read=58609
>>>> Total runtime: 6281.444 ms
>>>> (35 rows)
>>>>
>>>>
>>>> *2. Join each partition table with small table (par_list) and union
>>>> the result. This runs very fast. However, it's not reasonable if we union
>>>> 180 SELECT statements (for example, the date is from 2012-07-01 to
>>>> 2012-12-31. Any better suggestions.*
>>>> *
>>>> *
>>>> dailyest=# explain (analyze on, buffers on)
>>>> dailyest-# SELECT e.date, max(e.estimate)
>>>> dailyest-# FROM
>>>> dailyest-# par_list l,
>>>> dailyest-# par_est_2012_07_08 e
>>>> dailyest-# WHERE
>>>> dailyest-# l.id = e.list_id and
>>>> dailyest-# e.date = '2012-07-08' and
>>>> dailyest-# l.fid = 1 and
>>>> dailyest-# l.sid = 143441 and
>>>> dailyest-# l.cid in (36, 39, 6000) and
>>>> dailyest-# e.aid = 333710667
>>>> dailyest-# GROUP BY e.date
>>>> dailyest-# UNION ALL
>>>> dailyest-# SELECT e.date, max(e.estimate)
>>>> dailyest-# FROM
>>>> dailyest-# par_list l,
>>>> dailyest-# par_est_2012_07_09 e
>>>> dailyest-# WHERE
>>>> dailyest-# l.id = e.list_id and
>>>> dailyest-# e.date = '2012-07-09' and
>>>> dailyest-# l.fid = 1 and
>>>> dailyest-# l.sid = 143441 and
>>>> dailyest-# l.cid in (36, 39, 6000) and
>>>> dailyest-# e.aid = 333710667
>>>> dailyest-# GROUP BY e.date
>>>> dailyest-# UNION ALL
>>>> dailyest-# SELECT e.date, max(e.estimate)
>>>> dailyest-# FROM
>>>> dailyest-# par_list l,
>>>> dailyest-# par_est_2012_07_10 e
>>>> dailyest-# WHERE
>>>> dailyest-# l.id = e.list_id and
>>>> dailyest-# e.date = '2012-07-10' and
>>>> dailyest-# l.fid = 1 and
>>>> dailyest-# l.sid = 143441 and
>>>> dailyest-# l.cid in (36, 39, 6000) and
>>>> dailyest-# e.aid = 333710667
>>>> dailyest-# GROUP BY e.date
>>>> dailyest-# ;
>>>>
>>>>
>>>> QUERY PLAN
>>>>
>>>>
>>>> ----------------------------------------------------------------------------------------------------------------------------------------------
>>>> ------------------------------------------------------
>>>> Result (cost=0.00..91.49 rows=3 width=8) (actual time=83.736..254.912
>>>> rows=3 loops=1)
>>>> Buffers: shared hit=27 read=28
>>>> -> Append (cost=0.00..91.49 rows=3 width=8) (actual
>>>> time=83.735..254.910 rows=3 loops=1)
>>>> Buffers: shared hit=27 read=28
>>>> -> GroupAggregate (cost=0.00..30.48 rows=1 width=8) (actual
>>>> time=83.735..83.735 rows=1 loops=1)
>>>> Buffers: shared hit=9 read=12
>>>> -> Nested Loop (cost=0.00..30.47 rows=1 width=8)
>>>> (actual time=63.920..83.728 rows=2 loops=1)
>>>> Buffers: shared hit=9 read=12
>>>> -> Index Scan using par_list_sid_fid_cid_key on
>>>> par_list l (cost=0.00..18.56 rows=2 width=4) (actual time=1.540..1.550
>>>> rows=2 loops=1)
>>>> Index Cond: ((sid = 143441) AND (fid = 1)
>>>> AND (cid = ANY ('{36,39,6000}'::integer[])))
>>>> Buffers: shared hit=7 read=4
>>>> -> Index Only Scan using par_est_2012_07_08_pkey
>>>> on par_est_2012_07_08 e (cost=0.00..5.94 rows=1 width=12) (actual time=
>>>> 41.083..41.083 rows=1 loops=2)
>>>> Index Cond: ((date = '2012-07-08'::date) AND
>>>> (list_id = l.id) AND (aid = 333710667))
>>>> Heap Fetches: 0
>>>> Buffers: shared hit=2 read=8
>>>> -> GroupAggregate (cost=0.00..30.48 rows=1 width=8) (actual
>>>> time=76.911..76.911 rows=1 loops=1)
>>>> Buffers: shared hit=9 read=8
>>>> -> Nested Loop (cost=0.00..30.47 rows=1 width=8)
>>>> (actual time=57.580..76.909 rows=2 loops=1)
>>>> Buffers: shared hit=9 read=8
>>>> -> Index Scan using par_list_sid_fid_cid_key on
>>>> par_list l (cost=0.00..18.56 rows=2 width=4) (actual time=0.007..0.016
>>>> rows=2 loops=1)
>>>> Index Cond: ((sid = 143441) AND (fid = 1)
>>>> AND (cid = ANY ('{36,39,6000}'::integer[])))
>>>> Buffers: shared hit=7
>>>> -> Index Only Scan using par_est_2012_07_09_pkey
>>>> on par_est_2012_07_09 e (cost=0.00..5.94 rows=1 width=12) (actual
>>>> time=38.440..38.442 rows=1 loops=2)
>>>> Index Cond: ((date = '2012-07-09'::date) AND
>>>> (list_id = l.id) AND (aid = 333710667))
>>>> Heap Fetches: 0
>>>> Buffers: shared hit=2 read=8
>>>> -> GroupAggregate (cost=0.00..30.49 rows=1 width=8) (actual
>>>> time=94.262..94.262 rows=1 loops=1)
>>>> Buffers: shared hit=9 read=8
>>>> -> Nested Loop (cost=0.00..30.47 rows=1 width=8)
>>>> (actual time=74.393..94.259 rows=2 loops=1)
>>>> Buffers: shared hit=9 read=8
>>>> -> Index Scan using par_list_sid_fid_cid_key on
>>>> par_list l (cost=0.00..18.56 rows=2 width=4) (actual time=0.007..0.017
>>>> rows=2 loops=1)
>>>> Index Cond: ((sid = 143441) AND (fid = 1)
>>>> AND (cid = ANY ('{36,39,6000}'::integer[])))
>>>> Buffers: shared hit=7
>>>> -> Index Only Scan using par_est_2012_07_10_pkey
>>>> on par_est_2012_07_10 e (cost=0.00..5.95 rows=1 width=12) (actual
>>>> time=47.116..47.117 rows=1 loops=2)
>>>> Index Cond: ((date = '2012-07-10'::date) AND
>>>> (list_id = l.id) AND (aid = 333710667))
>>>> Heap Fetches: 0
>>>> Buffers: shared hit=2 read=8
>>>> Total runtime: 255.074 ms
>>>> (38 rows)
>>>>
>>>>
>>>
>> At first. you may try the following out and find out if the partition
> constraint exclusion is working or not::
>
> explain
> select *
> FROM
> par_est e
> WHERE
> e.date BETWEEN '2012-07-08' and '2012-07-10'
>