SolvedClickHouse Partition pruning not working as expected

How to reproduce

  • Which ClickHouse server version to use: v19.17.2.4-testing
DROP TABLE IF EXISTS test_partition_filtering;

CREATE TABLE test_partition_filtering (
    `timestamp` DateTime,
    zoneId UInt64
) ENGINE = MergeTree() 
ORDER BY (zoneId, timestamp);

INSERT INTO test_partition_filtering
  toUInt64(now())-1000*number/1000 as timestamp,
  number/1000 as zone
FROM numbers(1000000);

OPTIMIZE TABLE test_partition_filtering final;

SELECT count() FROM test_partition_filtering WHERE toDate(toStartOfDay(timestamp)) = today() and zoneId=42;
SELECT count() FROM test_partition_filtering WHERE toDate(timestamp) = today() AND zoneId = 42;

Expected behavior
Expect both SELECTS to prune partitions and to read just a single part.

Actual behavior
First query reads one part. Second query reads 12 parts.


executeQuery: (from SELECT count() FROM test_partition_filtering WHERE (toDate(toStartOfDay(timestamp)) = today()) AND (zoneId = 42)
InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "toDate(toStartOfDay(timestamp)) = today()" moved to PREWHERE
default.test_partition_filtering (SelectExecutor): Key condition: (column 0 in [42, 42]), (toDate(toStartOfDay(column 1)) in [18227, 18227]), and
default.test_partition_filtering (SelectExecutor): MinMax index condition: unknown, (toDate(toStartOfDay(column 0)) in [18227, 18227]), and
default.test_partition_filtering (SelectExecutor): Selected 1 parts by date, 1 parts by key, 1 marks to read from 1 ranges

executeQuery: (from SELECT count() FROM test_partition_filtering WHERE (toDate(timestamp) = today()) AND (zoneId = 42)
InterpreterSelectQuery: MergeTreeWhereOptimizer: condition "toDate(timestamp) = today()" moved to PREWHERE
default.test_partition_filtering (SelectExecutor): Key condition: (column 0 in [42, 42]), (toDate(column 1) in [18227, 18227]), and
default.test_partition_filtering (SelectExecutor): MinMax index condition: unknown, (toDate(column 0) in [18227, 18227]), and
default.test_partition_filtering (SelectExecutor): Selected 12 parts by date, 1 parts by key, 1 marks to read from 1 ranges
14 Answers

βœ”οΈAccepted Answer

It's fixed in #13497 . And toDate(timestamp_ms / 1000) will also work because of #14513 .

Related Issues:

ClickHouse DB::Exception: Too many parts (600). Merges are processing significantly slower than inserts
Each insert create a folder in /var/lib/clickhouse/.../table_name/ Inside that folder there are 2 fi...
ClickHouse Mac 10.15.4: Cannot find objcopy
@Renampme you can run the command below then find the file ~/ClickHouse/CMakeLists.txt then you can ...
ClickHouse Avro Support
+1 for Avro support Reopen #1342 for triage. ...
ClickHouse Any plans to support separation hot/cold data at the level of partitions?
@liuqian1989 it's kind of obvious possible feature but in practice ClickHouse works fast enough even...
ClickHouse Partition pruning not working as expected
It's fixed in #13497 And toDate(timestamp_ms / 1000) will also work because of #14513 . ...
amplify js Uncaught ReferenceError: global is not defined in latest Angular 6 RC
Just for reference I have passed through this issue with adding these lines on my index.html head: ...
react native firebase πŸ”₯(Android) Program type already present: io.invertase.firebase.BuildConfig
I think I found my ultimate error here In my package.json file I had at some point earlier ...
amplify cli Many-To-Many
You can implement many to many yourself using two 1-M @connections and a joining @model ...
superset Was unable to import superset Error: cannot import name '_maybe_box_datetimelike'
tx @Uneasy-listening !!! This worked for me: [only necessary if you have already installed pandas (p...
amplify js fetch is not defined
nodejs fix: I'm using amazon-cognito-auth-js with my express app and I'm following the case 1 exampl...
amplify js Error: No credentials, applicationId or region
I had the same issue (running on the latest Amplify v3) and worked around it by changing the followi...
amplify js Is it possible to get cognito user attributes in Lambda/cloud logic ?
I have been looking around for a while I feel the answers here didn't really answer the problem ...
react native firebase πŸ”₯ Version mismatch causing app termination
Looks like GoogleAppMeasurement gets imported as a dependency with version 5.3.0 A simple addition t...
react native firebase onNotificationOpened not working on Android (background/foreground)
Solved the issue only on background using @ZardozSpeaks approach Under my ...
grafana HTTP Error Bad Gateway when using prometheus
I still face this error after explicitly writing the URL (in my case it was http://localhost:9090) ...
amplify cli aws-exports.js is not generated
Even after the third read I find it utterly confusing and I have usability issues too ...
amplify js Auth Error: Amplify has not been configured correctly using Nuxt.js
I 'm having the same issue in aws-amplify: ^3.0.11 I found out Auth module didn't load configs of aw...
react native firebase Firebase dependency updates are required to fix gradle v4+ builds
@DeepaSriramRR as a temporal workaround you can disable version check of the Google plugin At the en...
react native firebase AndroidX support
Play Services just shipped AndroidX breaking changes - if you must upgrade your android Firebase SDK...
react native firebase RNFirebase core module was not found natively on ios
I will just leave it here in case someone comes and it's still struggling with this ...
amplify js How to refresh Cognito tokens
It will refresh if you call the SDK for it e.g. with Auth.currentSession() and it finds an expired t...
react native firebase [πŸ“š] AdMob - use @invertase/react-native-google-ads
Just FYI we're getting close here AdMob documentation availability Hi there! I've noticed that AdMob...
react native firebase [SOLVED with v2.1.1] Undefined symbols for architecture x86_6: _OBJC_CLASS_$_RNFirebaseDatabaseReference
Okay.. if anybody comes accross this magic error try this It solved the problem (for now) Close Xcod...
react native firebase [android] No Firebase app '[DEFAULT]' has been created - call firebase.initializeApp(), js engine: hermes
I spent a lot of hours for found who was the problem most setup issues such as default app has not b...
react native firebase iOS: Firebase.h not found
Can you first close xcode then delete the xcworkspace & Podfile.lock files in the ios directory then...
amplify cli @auth public/private IAM roles and other Providers
ok my bad was actually quite easy just do : and add a auth provider in my case was IAM ...
react native firebase Android: JDK 10 not supported for v4.1.0
+1 4.1.0 broke builds for Java10 As a temporary workaround on MacOS Issue Upon upgrading to v4.1.0 a...
amplify js Sign up multiple different accounts with the same email
The pre-signup trigger can be used to prevent the new signup from being created when there's an exis...
amplify js Amplify Console 200 (Rewrite) fails on SPA React (Router) Application
This worked for me source: </^((?!.(css|gif|ico|jpg|js|png|txt|svg|woff|ttf)$).)*$/> target address:...
react native firebase Notification not showing up in foreground after triggering displayNotification() (Android)
It seems as if the requirements for displaying a foreground notification are stricter on Android ...
amplify js aws-amplify 0.3.0: "Uncaught ReferenceError: require is not defined" when packaged with webpack
Ok I found something that helped Graphql-js uses .mjs as file extension which caused issues with the...
react native firebase Unable to instantiate service io.invertase.firebase.messaging.MessagingService: java.lang.ClassNotFoundException
My problem was solved by removing these lines from my AndroidManifest.xml Issue I've updated both RN...
react native firebase pod install fails after npm install @react-native-firebase/firestore
I tried the above solutions and didn't work for me I solved the issue by deleting the ./ios/Podfile....
react native firebase cannot find symbol BuildConfig.APPLICATION_ID
@mikehardy seems like it should be enough to rename APPLICATION_ID to LIBRARY_PACKAGE_NAME in /app/a...
amplify js Identity providers authentication against User Pools WITHOUT hosted UI
@martimarkov we find a solution for you to use the customized button to do that ...
react native firebase Error: You attempted to use a firebase module that's not installed on your Android project by calling firebase.notifications()
The solution for this is to add those to app/build.gradle : implementation '
react native firebase IOS unable to receive notification
It appears as if RNFirebase documentation is missing a few required steps Issue: Not able to receive...
amplify cli jest-haste-map: Haste module naming collision: -> namefunction <-
For React Native 0.6x configure the blacklist in metro.config.js instead of rn-cli.config.js as per ...
amplify js Getting "no current user" after successful login to Cognito UserPool
I had the same problem but for me removing the cookie storage configuration in aws-exports.js solved...
amplify js How to add user to Group
πŸ‘† Also we have achieved this using the Post Confirmation Lambda trigger Very simplified from our im...
react native firebase RNFirebaseNotifications.h file not found
I think in the header search paths it should be set to recursive for $(SRCROOT)/../node_modules/reac...
amplify js RFC: Amplify Library Modularization and Bundle Size Improvement
Modularization is available in Preview Hi all With us launching Modularization in the coming weeks ...
react native firebase Multiple dex files define Lcom/google/firebase/iid/zzb;
I got the same conflict with react-native-device-info and solved it with : After adding this package...
react native firebase Document how to mock react-native-firebase for jest
For now I've got this going on and it works for me (you may need to mock more or fewer modules or mo...
nativescript plugin firebase new iOS error on google auth - presenting view controller must be set
Google Auth was previously working on iOS but now I am receiving this error with no changes to codeb...
react native firebase Support Firebase SDK v5.0.0+
There are a number of breaking changed in v5 of the iOS pods: (
react native firebase πŸ”₯ [πŸ›] Crashlytics could not determine stripped/unstripped native library directories for project ':app'
Not sure if it is suitable for everyone but with my team with spotted that if in this step : https:/...
netdata Uninstall netdata?
I just removed it from some of my stuff (for now!) - Below paths are based on the defaults used to i...