Re: query on parent partition table has bad performance

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, August 21, 2014 12:13 AM
To: Huang, Suya
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] query on parent partition table has bad performance

"Huang, Suya" <Suya.Huang@au.experian.com> writes:
> I have a question about partition table query performance in postgresql, it's an old version 8.3.21, I know it's
alreadyout of support. so any words about the reason for the behavior would be very much appreciated. 

> I have a partition table which name is test_rank_2014_monthly and it has 7 partitions inherited from the parent
table,each month with one partition.  The weird thing is query out of the parent partition is as slow as query from a
non-partitionedtable, however, query from child table directly is really fast. 

> hitwise_uk=# explain analyze select * from test_rank_2014_07 r WHERE r.date = 201407 ;
>                                                             QUERY PLAN
> ----------------------------------------------------------------------
> ------------------------------------------------------------
>  Seq Scan on test_rank_2014_07 r  (cost=0.00..169797.75 rows=7444220 width=54) (actual time=0.007..1284.622
rows=7444220loops=1) 
>    Filter: (date = 201407)
>  Total runtime: 1831.379 ms
> (3 rows)

> -- query on parent table
> hitwise_uk=# explain analyze select * from test_rank_2014_monthly r WHERE r.date = 201407 ;
>
> QUERY PLAN
> ----------------------------------------------------------------------
> ----------------------------------------------------------------------
> --  Result  (cost=0.00..169819.88 rows=7444225 width=54) (actual
> time=0.009..4484.552 rows=7444220 loops=1)
>    ->  Append  (cost=0.00..169819.88 rows=7444225 width=54) (actual time=0.008..2495.457 rows=7444220 loops=1)
>          ->  Seq Scan on test_rank_2014_monthly r  (cost=0.00..22.12 rows=5 width=54) (actual time=0.000..0.000
rows=0loops=1) 
>                Filter: (date = 201407)
>          ->  Seq Scan on test_rank_2014_07 r  (cost=0.00..169797.75 rows=7444220 width=54) (actual
time=0.007..1406.600rows=7444220 loops=1) 
>                Filter: (date = 201407)  Total runtime: 5036.092 ms
> (7 rows)

The actual SeqScans are not very different in speed according to this.
Most of the extra time seems to be going into the Append and Result nodes.
Since those aren't actually doing anything except to return the input tuple up to their caller, I suspect what we're
lookingat here is mostly EXPLAIN ANALYZE's measurement overhead.  How much speed difference is there if you just do the
query,rather than EXPLAIN ANALYZE'ing it? 


> --query on non-partitioned table
> hitwise_uk=# explain analyze select * from rank_2014_monthly r WHERE r.date = 201407 ;
>                                                               QUERY
> PLAN
> ----------------------------------------------------------------------
> ----------------------------------------------------------------
>  Seq Scan on rank_2014_monthly r  (cost=0.00..1042968.85 rows=7424587 width=54) (actual time=3226.983..4537.974
rows=7444220loops=1) 
>    Filter: (date = 201407)
>  Total runtime: 5086.096 ms
> (3 rows)

You don't appear to be comparing apples to apples here.  Note the larger cost estimate, and the odd delay of more than
3seconds before the first row is returned.  Presumably what is happening is that this table contains gigabytes of dead
spacebefore the first live tuple.  You don't say how you made this comparison table, but I'll bet it involved deleting
dataand then loading fresh data without a VACUUM or TRUNCATE first. 


            regards, tom lane



===============================================================================================================================================================================

Thank you so much Tom for the valuable answer as always!

For the first point you made, you're right. The real execution time varies a lot from the explain analyze, the query on
parenttable are just as fast as it is on the child table.  is this a bug of explain analyze command? While we reading
theexecution plan, shall we ignore the top Append/Result nodes? 

For the second point, I created the test partition table using CTAS statement so there's no insert/update/delete on the
testtable. But on the production non-partition table, there might be such operations ran against them. But the reason
whyit takes 3 seconds to get the first row, might because it's non-partitioned so it has to scan the whole table to get
thefirst correct record? This non-partitioned table has ~ 30 million rows while the partition of the table  only has ~
5million rows. 


Thanks,
Suya