Ao Jianwang <aojw2008@gmail.com> writes:
> I found if we join the master table with other small table, then the
> running time is slow. While, if we join each child table with the small
> table, then it's very fast. Any comments and suggestions are greatly
> appreciated.
https://wiki.postgresql.org/wiki/Slow_Query_Questions
You haven't shown us table schemas, particularly the index definitions.
It looks to me like the partition child tables probably don't have
indexes that are well adapted to this query. Equality constraints
should be on leading columns of the index, but the only index I see
evidence of in your plans has the date column first. Probably the
planner is considering an inner-indexscan plan and rejecting it as
being more expensive than this one, because it would have to scan too
much of the index.
regards, tom lane