Thread: Querying w/ join slow for large/many child tables

On 1/29/19 6: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.
> 
> 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?

https://wiki.postgresql.org/wiki/Slow_Query_Questions

> 
> Thank you.
> 
> -- 
> Wells Oliver
> wells.oliver@gmail.com <mailto:wellsoliver@gmail.com>


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.