Re: partitioning performance tests after recent patches

Hi,

Thanks a lot for very exhaustive testing.

David already replied to some points, but let me comment on a couple of
points.

Please be advised that these may not be the numbers (or scalability
pattern of these numbers) you'll see when PG 12 is actually released,
because we may end up changing something that makes performance suffer a
bit. In particular, we are contemplating some changes around the safety
of planner's handling of cached partitioning metadata (in light of reduced
lock levels for adding/removing partitions) that might reduce the TPS
figure, the impact of which would worsen as the number of partitions
increases. Although, nothing is final yet; if interested, you can follow
that discussion at [1].

On 2019/04/15 4:19, Floris Van Nee wrote:
> The test cases were (see benchmark.sql for the SQL commands for setup and test cases):
> 1. Insert batches of 1000 rows per transaction
> 2. Simple SELECT query pruning on a static timestamp
> 3. The same SELECT query with static timestamp but with an added 'ORDER BY a, updated_at DESC LIMIT 1', which matches the index defined on the table
> 4. The same simple SELECT query, but now it's wrapped inside an inlineable SQL function, called with a static timestamp
> 5. The same simple SELECT query, but now it's wrapped inside a non-inlineable SQL function, called with a static timestamp
> 6. The same simple SELECT query, but now it's wrapped inside a plpgsql function, called with a static timestamp
> 7. Simple SELECT query pruning on a timestamp now()
> 8. The same SELECT query with dynamic timestamp but with an added 'ORDER BY a, updated_at DESC LIMIT 1', which matches the index defined on the table
> 9. The same simple SELECT query, but now it's wrapped inside an inlineable SQL function, called with a dynamic timestamp
> 10. The same simple SELECT query, but now it's wrapped inside a non-inlineable SQL function, called with a dynamic timestamp
> 11. The same simple SELECT query, but now it's wrapped inside a plpgsql function, called with a dynamic timestamp
> 12. The same query as 2) but then in an inlineable function
> 13. The same query as 3) but then in an inlineable function
> 14. A SELECT with nested loop (10 iterations) with opportunities for run-time pruning - some rows from a table are selected and the timestamp from rows in that table is used to join on another partitioned table
>
> The full results can be found in the attached file (results.txt). I also produced graphs of the results, which can be found on TimescaleDb's Github page [1]. Please take a look at these figures for an easy overview of the results. In general performance of HEAD looks really good.
>
> While looking at these results, there were a few questions that I couldn't answer.
> 1) It seems like the queries inside plpgsql functions (case 6 and 11) perform relatively well in PG11 compared to a non-inlineable SQL function (case 5 and 10), when a table consists of many partitions. As far as I know, both plpgsql and non-inlineable SQL functions are executed with generic plans. What can explain this difference? Are non-inlineable SQL function plans not reused between transactions, while plpgsql plans are?
> 2) Is running non-inlined SQL functions with a generic plan even the best option all the time? Wouldn't it be better to adopt a similar approach to what plpgsql does, where it tries to test if using a generic plan is beneficial? The non-inlineable SQL functions suffer a big performance hit for a large number of partitions, because they cannot rely on static planning-time pruning.

I'd never noticed this before. It indeed seems to be the case that SQL
functions and plpgsql functions are handled using completely different
code paths, of which only for the latter it's possible to use static
planning-time pruning.

> There was one other thing I noticed, and I believe it was raised by Tom in a separate thread as well: the setup code itself is really slow. Creating of partitions is taking a long time (it's taking several minutes to create the 4096 partition table).

Yeah that's rather bad. Thinking of doing something about that for PG 13.

Thanks,
Amit

[1] https://www.postgresql.org/message-id/27380.1555270166%40sss.pgh.pa.us