| From: | shihao zhong <zhong950419(at)gmail(dot)com> |
|---|---|
| To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
| Cc: | Alexandre Felipe <o(dot)alexandre(dot)felipe(at)gmail(dot)com>, yanarnold5(at)gmail(dot)com, pgsql-bugs(at)lists(dot)postgresql(dot)org, tomas(at)vondra(dot)me |
| Subject: | Re: BUG #19708: Hash Join becomes about 300x slower with higher work_mem |
| Date: | 2026-09-21 23:31:08 |
| Message-ID: | CAGRkXqQPXdWuVf5y8MesVCuFwDN=d616-t9E=eVDq5GXH76Wuw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
> Indeed. So I think this is an uninteresting contrived case.
I agree the reproducer is contrived, and that assuming
10 pages for a never vacuumed table is the right call.
My concern is that ANALYZE does not always get us out of it. The
n_distinct estimator is known to undershoot on long tailed columns.
I did a mini benchmark:
Take a 5M row orders table where half the rows come from 1000 big customers
and half from one time customers. Right after ANALYZE it gets n_distinct
31846, against a true value of 2.5M. A join of two such tables is
estimated at 780M rows and returns 2.5M. That is one join, and each
further join multiplies the error.
So the same shape comes out of fresh statistics on an ordinary schema,
and the extra cost only appeared in 18. That is why I think it is worth
handling.
Thanks,
Shihao
| From | Date | Subject | |
|---|---|---|---|
| Next Message | shihao zhong | 2026-09-21 23:49:51 | Re: BUG #19705: One NaN box makes a BRIN box_inclusion_ops index omit unrelated rows |
| Previous Message | Kirill Reshke | 2026-09-21 18:54:21 | Re: BUG #19700: PostgreSQL: an SP-GiST index on `inet` makes IPv6 rows invisible |