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)
Date: Fri, 15 Mar 2013 11:42:14 -0400
Message-ID: <6303.1363362134@sss.pgh.pa.us> (raw)
In-Reply-To: <CAAb+5fV4f8j3tbWoJqFXM_dxonxa0Qn9JYasGJt_7tEHGJjvnQ@mail.gmail.com>
References: <CAAb+5fV4f8j3tbWoJqFXM_dxonxa0Qn9JYasGJt_7tEHGJjvnQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, aojw2008@gmail.com
Subject: Re: Join the master table with other table is very slow (partitioning)
In-Reply-To: <6303.1363362134@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox