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

Hi Tom, Rumman

Here I use two levels of partition. That's, par_est is first partitioned by
monthly (such as par_est_2012_07, ...), then for each monthly child table,
we create the daily partition table (such as par_est_2012_07_01).
And, actually,
I did some test on that. The result is as follows.
*1) If postgres can join each child table (such as par_est_2012_07_08) with
the small table (par_list), then use par_est_2012_07_08_pkey can let the
postgres use index only scan (in UNION ALL), which is faster. However,
postgres doesn't do like that.*

dailyest=# \d par_est_2012_07_08
Table "public.par_est_2012_07_08"
Column | Type | Modifiers
----------+---------+-----------
list_id | integer | not null
aid | integer | not null
estimate | integer | not null
date | date | not null
Indexes:
"par_est_2012_07_08_pkey" PRIMARY KEY, btree (date, list_id, aid,
estimate) CLUSTER
Check constraints:
"par_est_2012_07_08_date_check" CHECK (date = '2012-07-12'::date)
"par_est_2012_07_date_check" CHECK (date >= '2012-07-01'::date AND date
<= '2012-07-31'::date)
Foreign-key constraints:
"par_est_2012_07_08_list_id_fk" FOREIGN KEY (list_id) REFERENCES
par_list(id)
Inherits: par_est_2012_07

dailyest=# \d par_list

Referenced by:
TABLE "par_est_2012_07_01" CONSTRAINT "par_est_2012_07_01_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)
TABLE "par_est_2012_07_02" CONSTRAINT "par_est_2012_07_02_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)
TABLE "par_est_2012_07_03" CONSTRAINT "par_est_2012_07_03_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)
TABLE "par_est_2012_07_04" CONSTRAINT "par_est_2012_07_04_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)
TABLE "par_est_2012_07_05" CONSTRAINT "par_est_2012_07_05_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)
TABLE "par_est_2012_07_06" CONSTRAINT "par_est_2012_07_06_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)
TABLE "par_est_2012_07_07" CONSTRAINT "par_est_2012_07_07_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)
TABLE "par_est_2012_07_08" CONSTRAINT "par_est_2012_07_08_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)
TABLE "par_est_2012_07_09" CONSTRAINT "par_est_2012_07_09_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)
TABLE "par_est_2012_07_10" CONSTRAINT "par_est_2012_07_10_list_id_fk"
FOREIGN KEY (list_id) REFERENCES par_list(id)

*2) As postgres just append the result from child tables and lastly join
with the small table. I change the index of the child table to the
following. So that the index can be used. However, it's still slower than
the "UNION ALL" solution. Any comments, thanks.*
dailyest=# \d par_est_2012_07_08
Table "public.par_est_2012_07_08"
Column | Type | Modifiers
----------+---------+-----------
list_id | integer | not null
aid | integer | not null
estimate | integer | not null
date | date | not null
Indexes:
"par_est_2012_07_08_aid_index" btree (aid)
"par_est_2012_07_08_le_index" btree (list_id, estimate) CLUSTER
Check constraints:
"par_est_2012_07_08_date_check" CHECK (date = '2012-07-08'::date)
"par_est_2012_07_date_check" CHECK (date >= '2012-07-01'::date AND date
<= '2012-07-31'::date)
Foreign-key constraints:
"par_est_2012_07_08_list_id_fk" FOREIGN KEY (list_id) REFERENCES
par_list(id)
Inherits: par_est_2012_07* *

On Fri, Mar 15, 2013 at 11:42 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ao Jianwang <aojw2008(at)gmail(dot)com> writes:
> > 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.
>
> https://wiki.postgresql.org/wiki/Slow_Query_Questions
>
> You haven't shown us table schemas, particularly the index definitions.
> It looks to me like the partition child tables probably don't have
> indexes that are well adapted to this query. Equality constraints
> should be on leading columns of the index, but the only index I see
> evidence of in your plans has the date column first. Probably the
> planner is considering an inner-indexscan plan and rejecting it as
> being more expensive than this one, because it would have to scan too
> much of the index.
>
> regards, tom lane
>