Обсуждение: 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.

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