Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1XKKeJ-0003w5-OX for pgsql-performance@arkaria.postgresql.org; Thu, 21 Aug 2014 05:10:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1XKKeJ-0008Cv-3s for pgsql-performance@arkaria.postgresql.org; Thu, 21 Aug 2014 05:10:59 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XKKeH-0008Co-Pl for pgsql-performance@postgresql.org; Thu, 21 Aug 2014 05:10:57 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.2:DHE_RSA_AES_256_CBC_SHA256:256) (Exim 4.80) (envelope-from ) id 1XKKeD-0008DE-BM for pgsql-performance@postgresql.org; Thu, 21 Aug 2014 05:10:55 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id s7L5Amx8032032; Thu, 21 Aug 2014 01:10:48 -0400 From: Tom Lane To: "Huang, Suya" cc: "pgsql-performance@postgresql.org" Subject: Re: query on parent partition table has bad performance In-reply-to: References: <9174.1408543968@sss.pgh.pa.us> Comments: In-reply-to "Huang, Suya" message dated "Thu, 21 Aug 2014 04:46:34 -0000" Date: Thu, 21 Aug 2014 01:10:48 -0400 Message-ID: <32031.1408597848@sss.pgh.pa.us> X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org "Huang, Suya" 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