Thread: Worse performance on partitioned table than in non partitioned table
Hi:
After partitioning a big table, I am getting slower performance on queries run on the non-partitioned table (llamadas) than the partitioned table (llamadas_maestra).
Not partitioned table:
heos_prod=# explain analyze select * from llamadas where cod_empresa=1 and fecha_llamada='20110622';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using llamadas_i06 on llamadas (cost=0.00..585218.30 rows=188287 width=540) (actual time=0.046..770.025 rows=309256 loops=1)
Index Cond: ((cod_empresa = 1) AND (fecha_llamada = '2011-06-22'::date))
Total runtime: 1119.274 ms
Partitioned table:
heos_prod=# explain analyze select * from llamadas_maestra where cod_empresa=1 and fecha_llamada='20110622';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..499268.95 rows=307688 width=854) (actual time=0.097..44919.308 rows=309256 loops=1)
-> Append (cost=0.00..499268.95 rows=307688 width=854) (actual time=0.088..43053.630 rows=309256 loops=1)
-> Seq Scan on llamadas_maestra (cost=0.00..10.60 rows=1 width=1988) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((cod_empresa = 1) AND (fecha_llamada = '2011-06-22'::date))
-> Index Scan using llamadas_201106_emp001_i01 on llamadas_201106_emp001 llamadas_maestra (cost=0.00..499258.35 rows=307687 width=854) (actual time=0.080..41998.749 rows=309256 loops=1)
Index Cond: ((cod_empresa = 1) AND (fecha_llamada = '2011-06-22'::date))
Total runtime: 45460.100 ms
Even on the table that inherits from the partitioned table the performance is lower:
heos_prod=# explain analyze select * from llamadas_201106_emp001 where cod_empresa=1 and fecha_llamada='20110622';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using llamadas_201106_emp001_i01 on llamadas_201106_emp001 (cost=0.00..499258.35 rows=307687 width=854) (actual time=0.053..897.431 rows=309256 loops=1)
Index Cond: ((cod_empresa = 1) AND (fecha_llamada = '2011-06-22'::date))
Total runtime: 1335.822 ms
(3 rows)
For informational purposes, these are the sizes of the tables and indexes:
Not partitioned table and index:
SELECT pg_size_pretty(pg_total_relation_size('llamadas'));
pg_size_pretty
----------------
30 GB
heos_prod=# SELECT pg_size_pretty(pg_total_relation_size('llamadas_i06'));
pg_size_pretty
----------------
6369 MB
Table that inherits from "master" partitioned table (and index):
heos_prod=# SELECT pg_size_pretty(pg_total_relation_size('llamadas_201106_emp001'));
pg_size_pretty
----------------
7100 MB
heos_prod=# SELECT pg_size_pretty(pg_total_relation_size('llamadas_201106_emp001_i01'));
pg_size_pretty
----------------
1279 MB
Partitioned table:
heos_prod=# SELECT pg_size_pretty(pg_total_relation_size('llamadas_maestra'));
pg_size_pretty
----------------
8192 bytes
Any ideas?
Thanks in advance.
After partitioning a big table, I am getting slower performance on queries run on the non-partitioned table (llamadas) than the partitioned table (llamadas_maestra).
Not partitioned table:
heos_prod=# explain analyze select * from llamadas where cod_empresa=1 and fecha_llamada='20110622';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using llamadas_i06 on llamadas (cost=0.00..585218.30 rows=188287 width=540) (actual time=0.046..770.025 rows=309256 loops=1)
Index Cond: ((cod_empresa = 1) AND (fecha_llamada = '2011-06-22'::date))
Total runtime: 1119.274 ms
Partitioned table:
heos_prod=# explain analyze select * from llamadas_maestra where cod_empresa=1 and fecha_llamada='20110622';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..499268.95 rows=307688 width=854) (actual time=0.097..44919.308 rows=309256 loops=1)
-> Append (cost=0.00..499268.95 rows=307688 width=854) (actual time=0.088..43053.630 rows=309256 loops=1)
-> Seq Scan on llamadas_maestra (cost=0.00..10.60 rows=1 width=1988) (actual time=0.003..0.003 rows=0 loops=1)
Filter: ((cod_empresa = 1) AND (fecha_llamada = '2011-06-22'::date))
-> Index Scan using llamadas_201106_emp001_i01 on llamadas_201106_emp001 llamadas_maestra (cost=0.00..499258.35 rows=307687 width=854) (actual time=0.080..41998.749 rows=309256 loops=1)
Index Cond: ((cod_empresa = 1) AND (fecha_llamada = '2011-06-22'::date))
Total runtime: 45460.100 ms
Even on the table that inherits from the partitioned table the performance is lower:
heos_prod=# explain analyze select * from llamadas_201106_emp001 where cod_empresa=1 and fecha_llamada='20110622';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using llamadas_201106_emp001_i01 on llamadas_201106_emp001 (cost=0.00..499258.35 rows=307687 width=854) (actual time=0.053..897.431 rows=309256 loops=1)
Index Cond: ((cod_empresa = 1) AND (fecha_llamada = '2011-06-22'::date))
Total runtime: 1335.822 ms
(3 rows)
For informational purposes, these are the sizes of the tables and indexes:
Not partitioned table and index:
SELECT pg_size_pretty(pg_total_relation_size('llamadas'));
pg_size_pretty
----------------
30 GB
heos_prod=# SELECT pg_size_pretty(pg_total_relation_size('llamadas_i06'));
pg_size_pretty
----------------
6369 MB
Table that inherits from "master" partitioned table (and index):
heos_prod=# SELECT pg_size_pretty(pg_total_relation_size('llamadas_201106_emp001'));
pg_size_pretty
----------------
7100 MB
heos_prod=# SELECT pg_size_pretty(pg_total_relation_size('llamadas_201106_emp001_i01'));
pg_size_pretty
----------------
1279 MB
Partitioned table:
heos_prod=# SELECT pg_size_pretty(pg_total_relation_size('llamadas_maestra'));
pg_size_pretty
----------------
8192 bytes
Any ideas?
Thanks in advance.
On Wed, Jul 20, 2011 at 12:09 PM, Ruben Blanco <rubenblan@gmail.com> wrote: > After partitioning a big table, I am getting slower performance on queries > run on the non-partitioned table (llamadas) than the partitioned table > (llamadas_maestra). > > > Not partitioned table: > > heos_prod=# explain analyze select * from llamadas where cod_empresa=1 > and fecha_llamada='20110622'; > QUERY > PLAN > > ------------------------------------------------------------------------------------------------------------------------------------------ > Index Scan using llamadas_i06 on llamadas (cost=0.00..585218.30 > rows=188287 width=540) (actual time=0.046..770.025 rows=309256 loops=1) > Index Cond: ((cod_empresa = 1) AND (fecha_llamada = > '2011-06-22'::date)) > Total runtime: 1119.274 ms > > > Partitioned table: > > heos_prod=# explain analyze select * from llamadas_maestra where > cod_empresa=1 and fecha_llamada='20110622'; > > > QUERY PLAN > > ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Result (cost=0.00..499268.95 rows=307688 width=854) (actual > time=0.097..44919.308 rows=309256 loops=1) > -> Append (cost=0.00..499268.95 rows=307688 width=854) (actual > time=0.088..43053.630 rows=309256 loops=1) > -> Seq Scan on llamadas_maestra (cost=0.00..10.60 rows=1 > width=1988) (actual time=0.003..0.003 rows=0 loops=1) > Filter: ((cod_empresa = 1) AND (fecha_llamada = > '2011-06-22'::date)) > -> Index Scan using llamadas_201106_emp001_i01 on > llamadas_201106_emp001 llamadas_maestra (cost=0.00..499258.35 rows=307687 > width=854) (actual time=0.080..41998.749 rows=309256 loops=1) > Index Cond: ((cod_empresa = 1) AND (fecha_llamada = > '2011-06-22'::date)) > Total runtime: 45460.100 ms > > > Even on the table that inherits from the partitioned table the performance > is lower: > > heos_prod=# explain analyze select * from llamadas_201106_emp001 where > cod_empresa=1 and fecha_llamada='20110622'; > > > QUERY PLAN > > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- > Index Scan using llamadas_201106_emp001_i01 on llamadas_201106_emp001 > (cost=0.00..499258.35 rows=307687 width=854) (actual time=0.053..897.431 > rows=309256 loops=1) > Index Cond: ((cod_empresa = 1) AND (fecha_llamada = > '2011-06-22'::date)) > Total runtime: 1335.822 ms > (3 rows) All 3 plans should be roughly the same since they all perform exactly the same index scan. If they aren't its most likely the effects of I/O or cacheing. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services