select count(1) from table where datecolum = '2020-02-01' slow

[luowencai]

cstore_fdw version:1.5

explain analyse select count(1) from table_tmp  where recdate = '2020-02-01';
                                                                       QUERY PLAN                                                                        
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=1536437.31..1536437.32 rows=1 width=8) (actual time=74387.526..74387.526 rows=1 loops=1)
   ->  Append  (cost=0.00..1536436.16 rows=460 width=0) (actual time=28608.772..74387.507 rows=152 loops=1)
         ->  Foreign Scan on table_tmp  (cost=0.00..0.03 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1)
               Filter: (recdate = '2020-02-01'::date)
               CStore File: /data/database/postgres/cstore_fdw/16384/312100
               CStore File Size: 0
         ->  Seq Scan on table_tmp_202002  (cost=0.00..1536436.14 rows=459 width=0) (actual time=28608.770..74387.491 rows=152 loops=1)
               Filter: (recdate = '2020-02-01'::date)
               Rows Removed by Filter: 13768558
 Planning time: 1.037 ms
 Execution time: 74387.669 ms

We use pg_pathman to partition table with month and use insert into table_tmp select * from to insert data.
While select count(1) or select *，it was very slow...It`s any thing we can fix this?

[mtuncer]

I am seeing that

               CStore File: /data/database/postgres/cstore_fdw/16384/312100
               CStore File Size: 0

looks like your data has never got to cstore_fdw table.

Can you share the partitioning information and PG version ?

I suggest using PG11 or later which has better partitioning support for foreign tables.

[luowencai]

select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-28), 64-bit

pg_pathman | 1.3 

[luowencai]

select create_range_partitions(
'logs_test.test_f'::regclass,
'time_column', '2018-07-01'::date, interval '1 month', 0, false);

NOTICE:  sequence "test_f_seq" does not exist, skipping
 create_range_partitions 
-------------------------
                       0
select add_range_partition(
'logs_test.test_f'::regclass, '2020-02-01'::date, '2020-03-01'::date, 'logs_test.test_f_2020-02-01');

\d+ logs_test.test_f;
                                      Foreign table "logs_test.test_f"
   Column    |            Type             | Modifiers | FDW Options | Storage | Stats target | Description 
-------------+-----------------------------+-----------+-------------+---------+--------------+-------------
 time_column | timestamp without time zone | not null  |             | plain   |              | 
Server: cstore_server
FDW Options: (compression 'pglz')
Child tables: logs_test."test_f_2020-02-01"


\d+ logs_test."test_f_2020-02-01";
                              Table "logs_test.test_f_2020-02-01"
   Column    |            Type             | Modifiers | Storage | Stats target | Description 
-------------+-----------------------------+-----------+---------+--------------+-------------
 time_column | timestamp without time zone | not null  | plain   |              | 
Check constraints:
    "pathman_test_f_2020-02-01_1_check" CHECK (time_column >= '2020-02-01 00:00:00'::timestamp without time zone AND time_column < '2020-03-01 00:00:00'::timestamp without time zone)
Inherits: logs_test.test_f

[luowencai]

Oh...I find the problem,Because I use add_range_partition to create a normal table.I should create foreign table and use attach_range_partition to attach parent table