From: Mats Olsen <mats@duneanalytics.com>
To: Sebastian Dressler <sebastian@swarm64.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Query Performance / Planner estimate off
Date: Wed, 28 Oct 2020 07:45:10 +0100
Message-ID: <1e0be097-7dd2-ead9-43cc-1a0fcb9097c7@duneanalytics.com> (raw)
In-Reply-To: <4CF9B275-8B82-48B5-94AD-BDB6FA5BF3C2@swarm64.com>
References: <CAARtqpGxzna+jj3LYNTacHxbC03-KdBs7XeQUaqwEkUJ+5QMbw@mail.gmail.com>
<FE09A620-390B-43DC-B73A-183790581443@swarm64.com>
<61c9fd35-bdb8-3fe6-bda0-81c7e6a9eaf8@duneanalytics.com>
<4CF9B275-8B82-48B5-94AD-BDB6FA5BF3C2@swarm64.com>
On 10/21/20 5:35 PM, Sebastian Dressler wrote:
> Hi Mats,
>
> Happy to help.
>
>> On 21. Oct 2020, at 16:42, Mats Olsen <mats@duneanalytics.com
>> <mailto:mats@duneanalytics.com>> wrote:
>> On 10/21/20 2:38 PM, Sebastian Dressler wrote:
>>> Hi Mats,
>>>
>>>> On 20. Oct 2020, at 11:37, Mats Julian Olsen
>>>> <mats@duneanalytics.com <mailto:mats@duneanalytics.com>> wrote:
>>>>
>>>> [...]
>>>>
>>>> 1) Vanilla plan (16 min) : https://explain.depesz.com/s/NvDR
>>>> <https://explain.depesz.com/s/NvDR;
>>>> 2) enable_nestloop=off (4 min): https://explain.depesz.com/s/buKK
>>>> <https://explain.depesz.com/s/buKK;
>>>> 3) enable_nestloop=off; enable_seqscan=off (2 min):
>>>> https://explain.depesz.com/s/0WXx <https://explain.depesz.com/s/0WXx;
>>>>
>>>> How can I get Postgres not to loop over 12M rows?
>>>
>>> I looked at the plans and your config and there are some thoughts
>>> I'm having:
>>>
>>> - The row estimate is off, as you possibly noticed. This can be
>>> possibly solved by raising `default_statistics_target` to e.g. 2500
>>> (we typically use that) and run ANALYZE
>> I've `set default_statistics_target=2500` and ran analyze on both
>> tables involved, unfortunately the plan is the same. The columns we
>> use for joining here are hashes and we expect very few duplicates in
>> the tables. Hence I think extended statistics (storing most common
>> values and histogram bounds) aren't useful for this kind of data.
>> Would you say the same thing?
>
> Yes, that looks like a given in this case.
>
>>>
>>> - I however think that the misestimate might be caused by the
>>> evt_tx_hash being of type bytea. I believe that PG cannot estimate
>>> this very well for JOINs and will rather pick row numbers too low.
>>> Hence the nested loop is picked and there might be no way around
>>> this. I have experienced similar things when applying JOINs on
>>> VARCHAR with e.g. more than 3 fields for comparison.
>>
>> This is very interesting, and I have never heard of issues with using
>> `bytea` for joins. Our entire database is filled with them, as we
>> deal with hashes of different lengths. In fact I would estimate that
>> 60% of columns are bytea's. My intuition would say that it's better
>> to store the hashes as byte arrays, rather than `text` fields as you
>> can compare the raw bytes directly without encoding first? Do you
>> have any references for this?
>>
> Unfortunately, I have not dealt yet with `bytea` that much. It just
> rang a bell when I saw these kind of off-estimates in combination with
> nested loops. In the case I referenced it was, that the tables had 3
> VARCHAR columns to be joined on and the estimate was very much off. As
> a result, PG chose nested loops in the upper layers of processing. Due
> to another JOIN the estimate went down to 1 row whereas it was 1
> million rows in reality. Now, yours is "only" a factor 5 away, i.e.
> this might be a totally different reason.
>
> However, I looked into the plan once more and realized, that the
> source of the problem could also be the scan on "Pair_evt_Mint" along
> the date dimension. Although you have a stats target of 10k there. If
> the timestamp is (roughly) sorted, you could try adding a BRIN index
> and by that maybe get a better estimate & scan-time.
Hi again, after around 48 hours a CREATE INDEX CONCURRENTLY ran
successfully. The new plan still uses a nested loop, but the scan on
"Pair_evt_Mint" is now a Parallel index scan. See
https://explain.depesz.com/s/8ZzT>>
>> Alternatively, since I know the length of the hashes in advance, I
>> could've used `varchar(n)`, but I don't think there's any gains to be
>> had in postgres by doing that? Something like `bytea(n)` would also
>> have been interesting, had postgres been able to exploit that
>> information.
>>
> I think giving VARCHAR a shot makes sense, maybe on an experimental
> basis to see whether the estimates get better. Maybe PG can then
> estimate that there are (almost) no dupes within the table but that
> there are N-many across tables. Another option to explore is maybe to
> use UUID as a type. As said above, it more looks like the timestamp
> causing the mis-estimate.
>
> Maybe try querying this table by itself with that timestamp to see
> what kind of estimate you get?
>
>>> - Other things to look into:
>>>
>>> - work_mem seems too low to me with 56MB, consider raising this
>>> to the GB range to avoid disk-based operations
>>> - min_parallel_table_scan_size - try 0
>>> - parallel_setup_cost (default 1000, maybe try 500)
>>> - parallel_tuple_cost (default 1.0, maybe try 0.1)
>>> - random_page_cost (as mentioned consider raising this maybe
>>> much higher, factor 10 or sth like this) or (typically)
>>> seq_page_cost can be possibly much lower (0.1, 0.01) depending on
>>> your storage
>>
>> I've tried various settings of these parameters now, and
>> unfortunately the only parameter that alters the query plan is the
>> last one (random_page_cost), which also has the side effect of
>> (almost) forcing sequential scans for most queries as far as I
>> understand? Our storage is Google Cloud pd-ssd.
>>
> I think a combination of random_page_cost with parallel_tuple_cost and
> min_parallel_table_scan_size might make sense. By that you possibly
> get at least parallel sequential scans. But I understand that this is
> possibly having the same effect as using `enable_nestloop = off`.
I'll have a closer look at these parameters.
Again, thank you.
Mats
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mats@duneanalytics.com, sebastian@swarm64.com
Subject: Re: Query Performance / Planner estimate off
In-Reply-To: <1e0be097-7dd2-ead9-43cc-1a0fcb9097c7@duneanalytics.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox