Worse performance on partitioned table than in non partitioned table

Started by Ruben Blancoover 14 years ago2 messagesgeneral
Jump to latest
#1Ruben Blanco
rubenblan@gmail.com

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.

#2Simon Riggs
simon@2ndQuadrant.com
In reply to: Ruben Blanco (#1)
Re: Worse performance on partitioned table than in non partitioned table

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