From: Tom Lane <tgl@sss.pgh.pa.us>
To: Huang, Suya <Suya.Huang@au.experian.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: query on parent partition table has bad performance
Date: Thu, 21 Aug 2014 01:10:48 -0400
Message-ID: <32031.1408597848@sss.pgh.pa.us> (raw)
In-Reply-To: <D83E55F5F4D99B4A9B4C4E259E6227CD014F6CEE@AUX1EXC02.apac.experian.local>
References: <D83E55F5F4D99B4A9B4C4E259E6227CD014F684C@AUX1EXC02.apac.experian.local>
<9174.1408543968@sss.pgh.pa.us>
<D83E55F5F4D99B4A9B4C4E259E6227CD014F6CEE@AUX1EXC02.apac.experian.local>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
"Huang, Suya" <Suya.Huang@au.experian.com> writes:
> For the first point you made, you're right. The real execution time varies a lot from the explain analyze, the query on parent table are just as fast as it is on the child table. is this a bug of explain analyze command? While we reading the execution plan, shall we ignore the top Append/Result nodes?
Well, it's a "bug" of gettimeofday(): it takes more than zero time, in
fact quite a lot more than zero time. Complain to your local kernel
hacker, and/or the chief of engineering at Intel. There aren't any
easy fixes available for us:
http://www.postgresql.org/message-id/flat/31856.1400021891@sss.pgh.pa.us> For the second point, I created the test partition table using CTAS statement so there's no insert/update/delete on the test table. But on the production non-partition table, there might be such operations ran against them. But the reason why it takes 3 seconds to get the first row, might because it's non-partitioned so it has to scan the whole table to get the first correct record? This non-partitioned table has ~ 30 million rows while the partition of the table only has ~ 5 million rows.
Oh, so the extra time is going into reading rows that fail the filter
condition? Well, that's not surprising. That's exactly *why* you
partition tables, so queries can skip entire child tables rather than
having to look at and reject individual rows.
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, Suya.Huang@au.experian.com
Subject: Re: query on parent partition table has bad performance
In-Reply-To: <32031.1408597848@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