From: Tom Lane <tgl@sss.pgh.pa.us>
To: Ao Jianwang <aojw2008@gmail.com>
cc: pgsql-performance@postgresql.org
Subject: Re: Join the master table with other table is very slow
 (partitioning)
In-reply-to: 
 <CAAb+5fV4f8j3tbWoJqFXM_dxonxa0Qn9JYasGJt_7tEHGJjvnQ@mail.gmail.com>
References: 
 <CAAb+5fV4f8j3tbWoJqFXM_dxonxa0Qn9JYasGJt_7tEHGJjvnQ@mail.gmail.com>
Comments: In-reply-to Ao Jianwang <aojw2008@gmail.com>
	message dated "Fri, 15 Mar 2013 23:02:28 +0800"
Date: Fri, 15 Mar 2013 11:42:14 -0400
Message-ID: <6303.1363362134@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance