public inbox for [email protected]
help / color / mirror / Atom feedPartitioned table - scans through every partitions
3+ messages / 2 participants
[nested] [flat]
* Partitioned table - scans through every partitions
@ 2017-08-25 15:36 Aniko Belim <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Aniko Belim @ 2017-08-25 15:36 UTC (permalink / raw)
To: 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 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partitioned table - scans through every partitions
@ 2017-08-25 15:44 Justin Pryzby <[email protected]>
parent: Aniko Belim <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Justin Pryzby @ 2017-08-25 15:44 UTC (permalink / raw)
To: Aniko Belim <[email protected]>; +Cc: pgsql-performance
On Fri, Aug 25, 2017 at 03:36:29PM +0000, Aniko Belim wrote:
> 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.
> 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.
https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS
|The following caveats apply to constraint exclusion:
| Constraint exclusion only works when the query's WHERE clause contains
| constants (or externally supplied parameters). For example, a comparison
| against a non-immutable function such as CURRENT_TIMESTAMP cannot be
| optimized, since the planner cannot know which partition the function value
| might fall into at run time.
...
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Partitioned table - scans through every partitions
@ 2017-08-25 16:08 Aniko Belim <[email protected]>
parent: Justin Pryzby <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Aniko Belim @ 2017-08-25 16:08 UTC (permalink / raw)
To: Justin Pryzby <[email protected]>; +Cc: pgsql-performance
Thank you, Justin!
Aniko
On 8/25/17, 10:44 AM, "Justin Pryzby" <[email protected]> wrote:
>On Fri, Aug 25, 2017 at 03:36:29PM +0000, Aniko Belim wrote:
>> 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.
>
>
>> 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.
>
>https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html#DDL-PARTITIONING-CAVEATS
>|The following caveats apply to constraint exclusion:
>| Constraint exclusion only works when the query's WHERE clause contains
>| constants (or externally supplied parameters). For example, a comparison
>| against a non-immutable function such as CURRENT_TIMESTAMP cannot be
>| optimized, since the planner cannot know which partition the function value
>| might fall into at run time.
>
>...
--
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-08-25 16:08 UTC | newest]
Thread overview: 3+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-08-25 15:36 Partitioned table - scans through every partitions Aniko Belim <[email protected]>
2017-08-25 15:44 ` Justin Pryzby <[email protected]>
2017-08-25 16:08 ` Aniko Belim <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox