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@xxxxxxxxxxxxx> wrote:
Ao Jianwang <aojw2008@xxxxxxxxx> writes:https://wiki.postgresql.org/wiki/Slow_Query_Questions
> 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.
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
