Re: Worse performance on partitioned table than in non partitioned table

On Wed, Jul 20, 2011 at 12:09 PM, Ruben Blanco <rubenblan(at)gmail(dot)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