Querying w/ join slow for large/many child tables

[Wells Oliver]

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?

Thank you.

-- 
Wells Oliver
wells.oli...@gmail.com 

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

[Adrian Klaver]

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.oli...@gmail.com 



--
Adrian Klaver
adrian.kla...@aklaver.com

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

[Ron]

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.