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

Ron <ronljohnsonjr@xxxxxxxxx> · Tue, 29 Jan 2019 23:58:53 -0600



    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.