From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Huang, Suya" <Suya(dot)Huang(at)au(dot)experian(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: query on parent partition table has bad performance |
Date: | 2014-08-21 05:10:48 |
Message-ID: | 32031.1408597848@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
"Huang, Suya" <Suya(dot)Huang(at)au(dot)experian(dot)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(dot)1400021891(at)sss(dot)pgh(dot)pa(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Kirkwood | 2014-08-21 10:02:03 | Re: Turn off Hyperthreading! WAS: 60 core performance with 9.3 |
Previous Message | Reza Taheri | 2014-08-21 04:51:42 | Re: High rate of transaction failure with the Serializable Isolation Level |