From: | Wells Oliver <wells(dot)oliver(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Querying w/ join slow for large/many child tables |
Date: | 2019-01-30 02:49:38 |
Message-ID: | CAOC+FBVMRLnbKCwMKpHYu3buHe2h2sztxS2fk7r_rBq=05nMFg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)oliver(at)gmail(dot)com <wellsoliver(at)gmail(dot)com>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2019-01-30 03:30:16 | Re: Querying w/ join slow for large/many child tables |
Previous Message | Michael Paquier | 2019-01-30 01:44:08 | Re: pg_rewind success even though getting error 'record with incorrect prev-link' |