Hi,
I haven’t caught the issue yet with this debug etc. in place, but auto_explain
(and some pg_stat_statements poking about) has helped me find something
interesting that might(?) be related.
My data ingest is in 2 functions, depending on the type of data:
- RADIUS data with usage info
- RADIUS data without usage info
The functions are the largely same, except the one with usage info has to go
and work with a table with lots of partitions (444, right at the moment).
The function that works with the usage info is *significantly* slower.
One thing I have specifically noticed is that the run time for the total
function doesn’t add up to the run time of each of the of the nested
statements. Not even close. It’s around 16ms on average (both in
pg_stat_statements and in the auto_explain), but the nested statements add up
to around 1-2ms or so - which I think means the planner is the culprit here.
I have been stepping through the various statements which are different between
the two functions, and note that when I do math on a timestamp in a SELECT
statement (i.e. _event_timestamp - INTERVAL ‘1 hour’), the planner takes 50ms
or so - note that the result of the timestamp is used to search the partition
key.
If I declare a function which does the math in advance, stores it in a variable
and then runs the SELECT, the planner takes less than 1ms.
Does this mean it’s calculating the timestamp for each partition, or something
like that?
I have updated the function in my production database to do this math in
advance, and the mean time is down to around 6ms, from 16ms.
This is still longer than the actual statement execution times in the
auto_explain output - which add up to around 1-2ms - but it’s better!
I’ve also turned on pg_stat_statements.track_planning and will see what that
looks like after some time.
I see Postgres 14 release notes has information about performance improvements
in the planner for updates on tables with "many partitions”. Is 444 partitions
“many”?
My updates are all impacting a single partition only.
> On 11/07/2022, at 6:20 PM, Nathan Ward wrote:
>
>>
>> On 11/07/2022, at 4:05 PM, Justin Pryzby wrote:
>>
>> On Mon, Jul 11, 2022 at 03:21:38PM +1200, Nathan Ward wrote:
Note that postgres doesn't automatically analyze parent tables, so you
should
maybe do that whenever the data changes enough for it to matter.
>>>
>>> Hmm. This raises some stuff I’m not familiar with - does analysing a parent
>>> table do anything?
>>
>> Yes
>>
>> You could check if you have stats now (maybe due to a global ANALYZE or
>> analyzedb) and how the query plans change if you analyze.
>> The transaction may be overly conservative.
>>
>> SELECT COUNT(1) FROM pg_stats WHERE tablename=PARENT;
>> SELECT last_analyze, last_autoanalyze, relname FROM pg_stat_all_tables WHERE
>> relname=PARENT;
>> begin;
>> SET default_statistics_target=10;
>> ANALYZE;
>> explain SELECT [...];
>> rollback;
>
> I have a development database which gets a mirror of about 50% of the data
> coming in, and ran a global ANALYZE earlier on - and note that the disk IO is
> actually a lot higher since which is interesting and not desirable obviously,
> so I have some more fiddling to do..
> The behaviour during the ANALYZE was very similar to what happens on my
> production database when things go funny though, so, this feels like it’s
> getting me close.
>
> The above is going to be a bit tricky to do I think - the ingest process runs
> a stored procedure, and behaviour varies quite a bit if I stick in synthetic
> values.
>
> I think probably my approach for now will be to turn on auto explain with
> some sampling, and see what happens.
>
>
> Side note, in the auto_explain docs, there is a note in a callout box saying
> that log_analyze has a high impact even if the query isn’t logged - if I use
> sampling, is this still the case - i.e. all queries are impacted - or is it
> only the sampled queries?
>
>>> I got the impression that analysing the parent was just shorthand for
>>> analysing all of the attached partitions.
>>
>> Could you let us know if the documentation left that impression ?
>>
>> See here (this was updated recently).
>>
>> https://www.postgresql.org/docs/13/sql-analyze.html#id-1.9.3.46.8
>>
>> For partitioned tables, ANALYZE gathers statistics by sampling rows from all
>> partitions; in addition, it will recurse into each partition and update its
>> statistics. Each leaf partition is analyzed only once, even with multi-level
>> partitioning. No statistics are collected for only the parent table (without
>> data from its partitions), because with partitioning it's guaranteed to be
>> empty.
>>
>> By contrast, if the table being analyzed has inheritance children, ANALYZE
>> gathers two sets of statistics: one on the rows of the parent table only,
>> and a second including rows of both the parent table and all of its
>> children. This second set of
