pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPartitioning and performance
3+ messages / 2 participants
[nested] [flat]
* Partitioning and performance
@ 2015-05-28 14:31 Ravi Krishna <sravikrishna3@gmail.com>
2015-05-28 14:41 ` Re: Partitioning and performance Ravi Krishna <sravikrishna3@gmail.com>
2015-05-28 18:05 ` Re: Partitioning and performance Jim Nasby <Jim.Nasby@BlueTreble.com>
0 siblings, 2 replies; 3+ messages in thread
From: Ravi Krishna @ 2015-05-28 14:31 UTC (permalink / raw)
To: pgsql-performance
I am testing partitioning of a large table. I am doing a range
partitioning based on a sequence col, which also acts as the primary
key. For inserts I am using a trigger which will redirect insert to
the right table based on the value of the primary key.
Based on my testing, I see that the insert speed is less than 10%
different than a non partitioned table. I am using SET
constraint_exclusion = on and I checked that via ANALYZE that the
planner does not consider non qualifying child tables.
yet, selects and updates based on the primary key show anywhere from
40 to 200% slowness as compared to non partition. One thing I notice
is that, even with partition pruning, the planner scans the base table
and the table matching the condition. Is that the additional overhead.
I am attaching below the output of analyze.
===========================
On a non partitioned table
explain select count(*) from tstesting.account where account_row_inst = 101 ;
Aggregate (cost=8.16..8.17 rows=1 width=0)
-> Index Only Scan using account_pkey on account (cost=0.14..8.16
rows=1 width=0)
Index Cond: (account_row_inst = 101)
(3 rows)
With partition pruning:
Aggregate (cost=8.45..8.46 rows=1 width=0)
-> Append (cost=0.00..8.44 rows=2 width=0)
-> Seq Scan on account (cost=0.00..0.00 rows=1 width=0)
Filter: (account_row_inst = 101)
-> Index Only Scan using account_part1_pkey on account_part1
(cost=0.42..8.44 rows=1 width=0)
Index Cond: (account_row_inst = 101)
(6 rows)
On a partitioned table, with no partition pruning.
explain analyze select count(*) from tstesting.account where
account_row_inst = 101 ;
Aggregate (cost=29.77..29.78 rows=1 width=0) (actual time=0.032..0.032
rows=1 loops=1)
-> Append (cost=0.00..29.76 rows=5 width=0) (actual time=0.029..0.029
rows=0 loops=1)
-> Seq Scan on account (cost=0.00..0.00 rows=1 width=0) (actual
time=0.000..0.000 rows=0 loops=1)
Filter: (account_row_inst = 101)
-> Index Only Scan using account_part1_pkey on account_part1
(cost=0.42..4.44 rows=1 width=0) (actual time=0.008..0.008 rows=0
loops=1)
Index Cond: (account_row_inst = 101)
Heap Fetches: 0
-> Index Only Scan using account_part2_pkey on account_part2
(cost=0.42..8.44 rows=1 width=0) (actual time=0.007..0.007 rows=0
loops=1)
Index Cond: (account_row_inst = 101)
Heap Fetches: 0
-> Index Only Scan using account_part3_pkey on account_part3
(cost=0.42..8.44 rows=1 width=0) (actual time=0.007..0.007 rows=0
loops=1)
Index Cond: (account_row_inst = 101)
Heap Fetches: 0
-> Index Only Scan using account_part4_pkey on account_part4
(cost=0.42..8.44 rows=1 width=0) (actual time=0.006..0.006 rows=0
loops=1)
Index Cond: (account_row_inst = 101)
Heap Fetches: 0
Planning time: 0.635 ms
Execution time: 0.137 ms
(18 rows)
--
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] 3+ messages in thread
* Re: Partitioning and performance
2015-05-28 14:31 Partitioning and performance Ravi Krishna <sravikrishna3@gmail.com>
@ 2015-05-28 14:41 ` Ravi Krishna <sravikrishna3@gmail.com>
1 sibling, 0 replies; 3+ messages in thread
From: Ravi Krishna @ 2015-05-28 14:41 UTC (permalink / raw)
To: pgsql-performance
On Thu, May 28, 2015 at 10:31 AM, Ravi Krishna <sravikrishna3@gmail.com> wrote:
> I am testing partitioning of a large table. I am doing a range
Sorry I forgot to clarify. I am using INHERITS for partioning with
check constraing built for range partitioning.
--
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] 3+ messages in thread
* Re: Partitioning and performance
2015-05-28 14:31 Partitioning and performance Ravi Krishna <sravikrishna3@gmail.com>
@ 2015-05-28 18:05 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
1 sibling, 0 replies; 3+ messages in thread
From: Jim Nasby @ 2015-05-28 18:05 UTC (permalink / raw)
To: Ravi Krishna <sravikrishna3@gmail.com>; pgsql-performance
On 5/28/15 9:31 AM, Ravi Krishna wrote:
> explain select count(*) from tstesting.account where account_row_inst = 101 ;
> Aggregate (cost=8.16..8.17 rows=1 width=0)
> -> Index Only Scan using account_pkey on account (cost=0.14..8.16
> rows=1 width=0)
> Index Cond: (account_row_inst = 101)
EXPLAIN only shows what the planner thinks a query will cost. For any
real testing, you need EXPLAIN ANALYZE.
Also, understand that partitioning isn't a magic bullet. It can make
some operations drastically faster, but it's not going to help every
scenario, and will actually slow some other operations down.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Data in Trouble? Get it in Treble! http://BlueTreble.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] 3+ messages in thread
end of thread, other threads:[~2015-05-28 18:05 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-05-28 14:31 Partitioning and performance Ravi Krishna <sravikrishna3@gmail.com>
2015-05-28 14:41 ` Ravi Krishna <sravikrishna3@gmail.com>
2015-05-28 18:05 ` Jim Nasby <Jim.Nasby@BlueTreble.com>
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