Re: query on parent partition table has bad 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
onparent table are just as fast as it is on the child table.  is this a bug of explain analyze command? While we
readingthe 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
thetest table. But on the production non-partition table, there might be such operations ran against them. But the
reasonwhy it takes 3 seconds to get the first row, might because it's non-partitioned so it has to scan the whole table
toget 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