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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote:
Ao Jianwang <aojw2008@xxxxxxxxx> 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


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux