Worse performance on partitioned table than in non partitioned table - Mailing list pgsql-general

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.