[PERFORM] Partitioned table - scans through every partitions - Mailing list pgsql-performance

Hi,

We have an issue with one of our partitioned tables. It has a column with timestamp without time zone type, and we had
topartition it daily. To do that, we created the following constraints like this example:
 
CHECK (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)


The problem we’re facing is no matter how we’re trying to select from it, it scans through every partitions.

Parent table:
         Table "public.dfp_in_network_impressions"
     Column      |            Type             | Modifiers 
-----------------+-----------------------------+-----------
 impression_time | timestamp without time zone | 
 nexus_id        | character varying           | 
 line_item_id    | bigint                      | 
 creative_id     | bigint                      | 
 ad_unit_id      | bigint                      | 
Triggers:
    insert_dfp_in_network_impressions_trigger BEFORE INSERT ON dfp_in_network_impressions FOR EACH ROW EXECUTE
PROCEDUREdfp_in_network_impressions_insert_function()
 
Number of child tables: 214 (Use \d+ to list them.)



One example of the child tables:
Table "dfp_in_network_impressions.dfp_in_network_impressions_20170202"
     Column      |            Type             | Modifiers 
-----------------+-----------------------------+-----------
 impression_time | timestamp without time zone | 
 nexus_id        | character varying           | 
 line_item_id    | bigint                      | 
 creative_id     | bigint                      | 
 ad_unit_id      | bigint                      | 
Indexes:
    "idx_dfp_in_network_impressions_20170202_creative_id" btree (creative_id)
    "idx_dfp_in_network_impressions_20170202_line_item_id" btree (line_item_id)
Check constraints:
    "dfp_in_network_impressions_20170202_impression_time_check" CHECK (to_char(impression_time, 'YYYYMMDD'::text) =
'20170202'::text)
Inherits: dfp_in_network_impressions



Confirmed that the records are in the correct partitions.

We even tried to query with the exact same condition as it is defined in the check constraint:
explain select * from dfp_in_network_impressions where to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Append  (cost=0.00..18655467.21 rows=3831328 width=45)
   ->  Seq Scan on dfp_in_network_impressions  (cost=0.00..0.00 rows=1 width=64)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170101  (cost=0.00..7261.48 rows=1491 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170219  (cost=0.00..20824.01 rows=4277 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170102  (cost=0.00..28899.83 rows=5935 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170220  (cost=0.00..95576.80 rows=19629 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170103  (cost=0.00..88588.22 rows=18194 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170221  (cost=0.00..116203.54 rows=23865 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170410  (cost=0.00..158102.98 rows=32470 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170531  (cost=0.00..116373.83 rows=23900 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170104  (cost=0.00..91502.48 rows=18792 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170222  (cost=0.00..106469.76 rows=21866 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170411  (cost=0.00..152244.92 rows=31267 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170601  (cost=0.00..117742.66 rows=24181 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170105  (cost=0.00..87029.80 rows=17874 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170223  (cost=0.00..105371.79 rows=21641 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
   ->  Seq Scan on dfp_in_network_impressions_20170412  (cost=0.00..143897.43 rows=29553 width=45)
         Filter: (to_char(impression_time, 'YYYYMMDD'::text) = '20170202'::text)
… Etc.

It scans through every partitions. Shouldn’t it only scan the
dfp_in_network_impressions.dfp_in_network_impressions_20170202child table? Or we missing something?
 
Any advice/help would highly appreciated.

System details:
Postgres version: PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat
4.4.7-11),64-bit
 
The constraint_exclusion parameter is set to partition, but same behavior when I set it to “on”.

SELECT name, current_setting(name), SOURCE
  FROM pg_settings
  WHERE SOURCE NOT IN ('default', 'override’);

             name             |             current_setting             |        source        
------------------------------+-----------------------------------------+----------------------
 application_name             | psql                                    | client
 archive_command              | /var/db/wal_archive.sh %p %f | configuration file
 archive_mode                 | on                                      | configuration file
 autovacuum_naptime           | 1min                                    | configuration file
 checkpoint_completion_target | 0.9                                     | configuration file
 checkpoint_segments          | 32                                      | configuration file
 client_encoding              | UTF8                                    | client
 DateStyle                    | ISO, MDY                                | configuration file
 default_text_search_config   | pg_catalog.english                      | configuration file
 effective_cache_size         | 96GB                                    | configuration file
 huge_pages                   | try                                     | configuration file
 lc_messages                  | en_US.UTF-8                             | configuration file
 lc_monetary                  | en_US.UTF-8                             | configuration file
 lc_numeric                   | en_US.UTF-8                             | configuration file
 lc_time                      | en_US.UTF-8                             | configuration file
 listen_addresses             | *                                       | configuration file
 log_autovacuum_min_duration  | 0                                       | configuration file
 log_checkpoints              | on                                      | configuration file
 log_connections              | on                                      | configuration file
 log_destination              | stderr                                  | configuration file
 log_directory                | /var/log/postgresql                     | configuration file
 log_duration                 | on                                      | configuration file
 log_file_mode                | 0640                                    | configuration file
 log_filename                 | postgresql-%Y%m%d.log                   | configuration file
 log_line_prefix              | %t [%p]: [%l-1] %h %d %u                | configuration file
 log_lock_waits               | on                                      | configuration file
 log_min_duration_statement   | 100ms                                   | configuration file
 log_min_error_statement      | warning                                 | configuration file
 log_min_messages             | warning                                 | configuration file
 log_rotation_age             | 1d                                      | configuration file
 log_rotation_size            | 0                                       | configuration file
 log_statement                | ddl                                     | configuration file
 log_timezone                 | US/Central                              | configuration file
 log_truncate_on_rotation     | on                                      | configuration file
 logging_collector            | on                                      | configuration file
 maintenance_work_mem         | 1GB                                     | configuration file
 max_connections              | 110                                     | configuration file
 max_locks_per_transaction    | 256                                     | configuration file
 max_stack_depth              | 2MB                                     | environment variable
 max_wal_senders              | 3                                       | configuration file
 port                         | 5432                                    | configuration file
 shared_buffers               | 64GB                                    | configuration file
 TimeZone                     | US/Central                              | configuration file
 track_activities             | on                                      | configuration file
 track_counts                 | on                                      | configuration file
 track_functions              | none                                    | configuration file
 track_io_timing              | off                                     | configuration file
 wal_keep_segments            | 2000                                    | configuration file
 wal_level                    | hot_standby                             | configuration file
 work_mem                     | 768MB                                   | configuration file




Linux 2.6.32-504.30.3.el6.x86_64 #1 SMP Wed Jul 15 10:13:09 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux



Thank you!
Aniko