From: | Ron <ronljohnsonjr(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Querying w/ join slow for large/many child tables |
Date: | 2019-01-30 05:58:53 |
Message-ID: | 91a008bf-180b-f60b-b8e1-39c32537baee@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 1/29/19 8:49 PM, Wells Oliver wrote:
> I have a primary parent table with a child table per week of the year for
> each week back through 2015. There are a lot of child tables. Each week's
> child table has maybe 80-110m rows.
A *partititioned* table?
>
> When I join to the parent table on a column, it's very slow, but when I
> manually specify the specific week's child table, it's quite fast, e.g.
>
> Slow:
> select * from foo
> join schema.mytable on foo.col = mytable.col
>
> vs. fast:
> select * from foo
> join schema.mytable_2015_wk33 as mytable on foo.col = mytable.col
>
> What's the resolution to make querying the table faster in terms of it
> finding the appropriate child table? Is it putting an index on `col` on
> each child table? Some other thing?
If it's a partitioned table, then yes, putting the appropriate indexes on
each child table as well as the empty parent table is what we do.
--
Angular momentum makes the world go 'round.
From | Date | Subject | |
---|---|---|---|
Next Message | Ron | 2019-01-30 06:02:35 | Re: pg_rewind success even though getting error 'record with incorrect prev-link' |
Previous Message | Abdullah Al Maruf | 2019-01-30 03:57:56 | Re: pg_rewind success even though getting error 'record with incorrect prev-link' |