RE: Very long query planning times for database with lots of partitions
Do you have constraint_exclusion set correctly (i.e. ‘on’ or ‘partition’)? If so, does the EXPLAIN output mention all of your parent partitions, or are some being successfully pruned? Planning times can be sped up significantly if the planner can exclude parent partitions, without ever having to examine the constraints of the child (and grandchild) partitions. If this is not the case, take another look at your query and try to figure out why the planner might believe a parent partition cannot be outright disregarded from the query – does the query contain a filter on the parent partitions’ partition key, for example? I believe Timescaledb has its own query planner optimisations for discarding partitions early at planning time. Good luck, Steve. This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you. GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice.
GCC 8.3.0 vs. 9.0.1
Hi, (Apologies if this isn't the right place to post this) A few days ago a blog post appeared on phoronix.com[1] comparing GCC 8.3.0 against 9.0.1 on Intel cascadelake processors. A notable difference was seen in the PostgreSQL benchmark (v10.3, pgbench, read/write, more detail below), both when compiling with -march=native and -march=skylake: GCC version | -march= | TPS 8.3.0 | skylake | 5667 9.0.1 | skylake | 11684 (2.06x speed up) 8.3.0 | native | 8075 9.0.1 | native | 11274 (1.40x speed up) I'm interested to know the devs' take on this is - does GCC 9 contain some new feature(s) that are particularly well suited to compiling and optimising Postgres? Or was GCC 8 particularly bad? The test script seems to be this one[2], and goes something like this: - Postgres 10.3 is configure using --without-readline and --without-zlib (after patching it so that it can run as root). The remaining compiler options seem to be (implicitly?) "-fno-strict-aliasing -fwrapv -O3 -lpgcommon -lpq -lpthread -lrt -lcrypt -ldl -lm", plus the -march setting under test. - initdb is run with --encoding=SQL_ASCII --locale=C - the db is started with "pg_ctl start -o '-c autovacuum=false'" - createdb pgbench - pgbench -i -s pgbench - pgbench -j -c -T 60 pgbench Cheers, Steven. [1] https://www.phoronix.com/scan.php?page=news_item&px=Intel-Cascade-Lake-GCC9 [2] https://openbenchmarking.org/innhold/b53a0ca6dcfdc9b8597a7b144fae2110fa6af1fb This email is confidential. If you are not the intended recipient, please advise us immediately and delete this message. The registered name of Cantab- part of GAM Systematic is Cantab Capital Partners LLP. See - http://www.gam.com/en/Legal/Email+disclosures+EU for further information on confidentiality, the risks of non-secure electronic communication, and certain disclosures which we are required to make in accordance with applicable legislation and regulations. If you cannot access this link, please notify us by reply message and we will send the contents to you. GAM Holding AG and its subsidiaries (Cantab – GAM Systematic) will collect and use information about you in the course of your interactions with us. Full details about the data types we collect and what we use this for and your related rights is set out in our online privacy policy at https://www.gam.com/en/legal/privacy-policy. Please familiarise yourself with this policy and check it from time to time for updates as it supplements this notice.
RE: GCC 8.3.0 vs. 9.0.1
Thanks, everyone, for your comments. I guess if something looks too good to be true then it usually is! Steven. (P.S. Apologies for the email disclaimer - it is added by our mail server, not my mail client, and its exclusion list is on the fritz)
RE: Shortest offline window on database migration
>Has anyone been through this type of problem? > You could set up a new, empty db (with checksums enabled, etc.) on the new hardware and then use logical replication to sync across all the data from the existing cluster. (This logical replica could be doing binary replication to hot standbys too, if you like). When the sync has finished you could perhaps gradually shift read-only load over to the new db, and finally switch write load too - your downtime would then be limited to how long this final cut-over takes. Steve.
RE: scans on table fail to be excluded by partition bounds
> ts=# explain SELECT * FROM eric_enodeb_cell_metrics WHERE start_time > BETWEEN '2019-01-01 04:00' AND '2019-01-01 05:00' OR start_time BETWEEN > '2019-01-02 04:00' AND '2019-01-02 05:00' Maybe it's because of the implicit usage of the local timezone when the strings are cast to (timestamp with time zone) in the values you give for start_time here? What happens if you specify it using "TIMESTAMP WITH TIME ZONE '2019-01-01 04:00-05'", etc.? Steve.
