On Mon, 9 Mar 2020 at 05:05, Mariel Cherkassky <mariel.cherkassky@xxxxxxxxx> wrote: > PG12 : > Planning Time: 8.157 ms > Execution Time: 2.920 ms > (22 rows) > > > PG96 : > Planning time: 0.815 ms > Execution time: 0.158 ms > (12 rows) 8 ms seems pretty slow to planning that query. Does the planning time drop if you execute this multiple times in the same session? Does the time change if you try again without any foreign keys? The planning time for the partitioned case is certainly going to take longer. Partitioned tables are never going to improve the times of query planning. It's only possible that they'll improve the performance during query execution. For such small fast queries as the ones you've shown, it's important to remember that more complex query plans (ones with more nodes) do lead to longer times for executor startup and shutdown. EXPLAIN (without ANALYZE), will perform query planning and executor startup/shutdown. If you enable \timing on in psql and test the EXPLAIN performance of these queries in each version, then you might get an idea of where the overheads are. Additionally, you're unlikely to see performance improvements with table partitioning unless you're accessing many rows and partitioning allows the data locality of the rows that you are accessing to improve. i.e accesses fewer buffers and/or improves cache hit ratios. In PG12, if the partition pruning can be done during query planning then the planning and executor startup overhead is much lower since there are fewer relations to generate access paths for and fewer nodes in the final plan. This also improves the situation during execution as it means fewer locks to take and fewer nodes to startup/shutdown. > As you can see, the results are better in pg96. This example only shows the results for a small data set. In bigger data sets I get a bigger diff... Can you share the results of that? > I tried changing many postgresql.conf parameters that were added (max_workers_per_gather,enable_partitionwise_join and so on..). The former only does anything for parallel queries. None of the plans you've shown are parallel ones. The latter also does not count in this case. It only counts when joining two identically partitioned tables. > I dont understand why in pg12 it scans all the partitions instead of the relevant one.. If you'd specified a specific "device" in the query SQL, then the query planner would know which partition to scan for that particular device. However, since you're looking up the device in another table and performing a join, the device is only known during query execution. The plan nodes for the non-matching partitions do go through executor startup, but they're not scanned during execution, as you've seen with the "(never executed)" appearing in the EXPLAIN ANALYZE output. Since executor startup occurs before execution, the device you mean is still unknown during executor startup, so the executor must startup the nodes for all partitions that are in the plan. Starting up a plan node is not free, but not really very expensive either. However, the overhead of it might be quite large proportionally in your case since the executor is doing so little work. The most basic guidelines for table partitioning are, don't partition your tables unless it's a net win. If partitioning was always faster, we'd just have designed Postgres to implicitly partition all of your tables for you. There are some other guidelines in [1]. [1] https://www.postgresql.org/docs/12/ddl-partitioning.html#DDL-PARTITIONING-DECLARATIVE-BEST-PRACTICES David
