Timeline for Very bad query plan in PostgreSQL 9.6
Current License: CC BY-SA 4.0
15 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 16, 2021 at 17:48 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
typos, denoise
|
| Mar 29, 2021 at 11:31 | comment | added | Erwin Brandstetter | I think it makes a difference. That's why I brought it up. | |
| Mar 29, 2021 at 9:08 | comment | added | gozdal |
> the predicate volume_id = ANY(volume_ids) should rather be applied to the comparatively tiny temp table Do you think this changes how PostgreSQL plans this query? The typical scenario is that there is a single distinct value there in the temp table.
|
|
| Mar 27, 2021 at 1:49 | comment | added | Erwin Brandstetter |
I see, more misunderstandings concerning statistics. Distracting from the more important part: the predicate volume_id = ANY(volume_ids) should rather be applied to the comparatively tiny temp table.
|
|
| Mar 26, 2021 at 10:29 | comment | added | gozdal |
The main problem I see is that the merge condition Merge Cond: (dir.volume_id = dir_process.volume_id) will be extremely inefficient and I don't understand how it's chosen. Had it been Merge Cond: (dir.parent_id = dir_process.id) then it would have made much more sense to me.
|
|
| Mar 26, 2021 at 10:25 | comment | added | gozdal |
n_distinct is set on dir_current.parent_id to -0.1 whenever DB is created. ANALYZE on dir_current are run regularly on the table. dir_process is analyzed once, table is immutable and statistics on it are not altered. Looking at the query plans I've pasted I believe the statistics themselves are doing good job at estimating: dir_current_volumeid_id_unq on dir_current dir (cost=0.12..922747.05 rows=624805 width=456) (actual rows=1231600 loops=1)
|
|
| Mar 26, 2021 at 10:19 | comment | added | gozdal | Thanks, Erwin. I've read your revised answer and it seems I have created some confusion. | |
| Mar 24, 2021 at 0:20 | comment | added | Erwin Brandstetter |
@gozdal: Obviously, I had main table and temp table confused for my advise concerning ANALYZE and statistics. Plus, your comments made my Spidey sense tingle. So I adapted my first paragraph. A lot.
|
|
| Mar 24, 2021 at 0:18 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
fix misguided advise on statistics; add a lot more
|
| Mar 23, 2021 at 13:21 | comment | added | gozdal |
dir_current is analyzed and statistics are set according to our experience (i.e. there is 10 rows on average with a given parent_id so n_distinct is set to -0.1). Given volume_id OTOH has millions of rows.
|
|
| Mar 23, 2021 at 13:09 | comment | added | gozdal |
The temp table is immutable: it's created once, analyzed and never changes. It exists because we had a problem with plain query being inefficient, so we extracted the rows we're interested in to a temp table to have statistics about it. Then the second query is used to find rows from dir_current which are children of those interesting ones (i.e. dir_current.parent_id=dir_process.id)
|
|
| Mar 23, 2021 at 0:43 | comment | added | Erwin Brandstetter | @gozdal: If you fix up column statistics like I addressed in my first paragraph, and the problem persists, then maybe you are on to something. But all bets are off while you operate on outdated statistics. The rest of my post suggests a different approch that should be more efficient, regardless. | |
| Mar 22, 2021 at 9:26 | comment | added | gozdal | I understand that 13.2 is way better and we're in the process of migrating the product to 13.2 with tables partitioned over volume_id. However, this query smells to me like a potential bug in the PG itself - merge join using very inefficient condition. I am having hard time understanding why such a poor merge condition could be chosen by the planner. We've seen it replicated recently when testing partitioned DB with PG 13.2 (with exactly same query) - but again it's hard to catch is it happens even more rarely. | |
| Mar 20, 2021 at 1:00 | comment | added | Erwin Brandstetter | Postgres 9.6 is reaches EOL this year. Current versions have gotten substantially better with big data (among other things). Consider upgrading ASAP. (You said you don't have control, but suggest this to whoever has.) | |
| Mar 20, 2021 at 0:59 | history | answered | Erwin Brandstetter | CC BY-SA 4.0 |