From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: query on parent partition table has bad performance
Date: Wed, 20 Aug 2014 06:49:38 -0700 (PDT)
Message-ID: <1408542578240-5815552.post@n5.nabble.com> (raw)
In-Reply-To: <D83E55F5F4D99B4A9B4C4E259E6227CD014F684C@AUX1EXC02.apac.experian.local>
References: <D83E55F5F4D99B4A9B4C4E259E6227CD014F684C@AUX1EXC02.apac.experian.local>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: david.g.johnston@gmail.com
Subject: Re: query on parent partition table has bad performance
In-Reply-To: <1408542578240-5815552.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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