Hi,
We have an issue with one of our partitioned tables. It has a column with
timestamp without time zone type, and we had to partition 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 PROCEDURE
dfp_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_20170202 child 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
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
