pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedquery on parent partition table has bad performance
5+ messages / 3 participants
[nested] [flat]
* query on parent partition table has bad performance
@ 2014-08-20 09:30 Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 2 replies; 5+ messages in thread
From: Huang, Suya @ 2014-08-20 09:30 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have a question about partition table query performance in postgresql, it's an old version 8.3.21, I know it's already out 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-partitioned table, however, query from child table directly is really fast.
have no idea... is this an expected behavior of partition table in old releases?
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=7444220 loops=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=0 loops=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.600 rows=7444220 loops=1)
Filter: (date = 201407)
Total runtime: 5036.092 ms
(7 rows)
--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=7444220 loops=1)
Filter: (date = 201407)
Total runtime: 5086.096 ms
(3 rows)
check constraints on child table is something like below:
...
Check constraints:
"test_rank_2014_07_date_check" CHECK (date = 201407)
Inherits: test_rank_2014_monthly
Thanks,
Suya
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query on parent partition table has bad performance
@ 2014-08-20 13:49 David G Johnston <david.g.johnston@gmail.com>
parent: Huang, Suya <Suya.Huang@au.experian.com>
1 sibling, 0 replies; 5+ messages in thread
From: David G Johnston @ 2014-08-20 13:49 UTC (permalink / raw)
To: pgsql-performance
Huang, Suya wrote
> Hi,
>
> I have a question about partition table query performance in postgresql,
> it's an old version 8.3.21, I know it's already out 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-partitioned table, however, query from child table directly is
> really fast.
>
> have no idea... is this an expected behavior of partition table in old
> releases?
>
>
> 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=7444220 loops=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=0 loops=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.600 rows=7444220 loops=1)
> Filter: (date = 201407)
> Total runtime: 5036.092 ms
> (7 rows)
>
> --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=7444220 loops=1)
> Filter: (date = 201407)
> Total runtime: 5086.096 ms
> (3 rows)
>
>
> check constraints on child table is something like below:
> ...
> Check constraints:
> "test_rank_2014_07_date_check" CHECK (date = 201407)
> Inherits: test_rank_2014_monthly
>
> Thanks,
> Suya
Given that the 2nd and 3rd queries perform about equal the question is why
the first query performs so much better. I suspect you are not taking any
care to avoid caching effects and so that it what you are seeing. Its hard
to know for sure whether you ran the three queries in the order
listed...which if so would likely negate this theory somewhat.
Adding (BUFFERS) to your explain would at least give some visibility into
caching effects - though since that is only available in supported versions
that is not an option for you. Still, it is the most likely explanation for
what you are seeing.
There is time involved to process the partition constraint exclusion but I'm
doubting it accounts for a full 3 seconds...
David J.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/query-on-parent-partition-table-has-bad-performance-tp581552...
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query on parent partition table has bad performance
@ 2014-08-20 14:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Huang, Suya <Suya.Huang@au.experian.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2014-08-20 14:12 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: pgsql-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 already out 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-partitioned table, 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=7444220 loops=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=0 loops=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.600 rows=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 looking at 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=7444220 loops=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 3 seconds before the first
row is returned. Presumably what is happening is that this table contains
gigabytes of dead space before the first live tuple. You don't say how
you made this comparison table, but I'll bet it involved deleting data
and then loading fresh data without a VACUUM or TRUNCATE first.
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query on parent partition table has bad performance
@ 2014-08-21 04:46 Huang, Suya <Suya.Huang@au.experian.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Huang, Suya @ 2014-08-21 04:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-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 already out 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-partitioned table, 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=7444220 loops=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=0 loops=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.600 rows=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 looking at 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=7444220 loops=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 3 seconds before the first row is returned. Presumably what is happening is that this table contains gigabytes of dead space before the first live tuple. You don't say how you made this comparison table, but I'll bet it involved deleting data and 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 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?
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.
Thanks,
Suya
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: query on parent partition table has bad performance
@ 2014-08-21 05:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Huang, Suya <Suya.Huang@au.experian.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2014-08-21 05:10 UTC (permalink / raw)
To: Huang, Suya <Suya.Huang@au.experian.com>; +Cc: pgsql-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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2014-08-21 05:10 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-08-20 09:30 query on parent partition table has bad performance Huang, Suya <Suya.Huang@au.experian.com>
2014-08-20 13:49 ` David G Johnston <david.g.johnston@gmail.com>
2014-08-20 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-08-21 04:46 ` Huang, Suya <Suya.Huang@au.experian.com>
2014-08-21 05:10 ` Tom Lane <tgl@sss.pgh.pa.us>
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