From: | jian he <jian(dot)universality(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Paul A Jungwirth <pj(at)illuminatedcomputing(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Improve rowcount estimate for UNNEST(column) |
Date: | 2023-11-27 07:05:57 |
Message-ID: | CACJufxEax35yb0eVfdHQ-xus6YVK-rEDjTXj1BdbBe3-mmtcFA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi.
Since both array_op_test, arrest both are not dropped at the end of
src/test/regress/sql/arrays.sql.
I found using table array_op_test test more convincing.
select
reltuples * 10 as original,
reltuples * (select
floor(elem_count_histogram[array_length(elem_count_histogram,1)])
from pg_stats
where tablename = 'array_op_test' and attname = 'i')
as with_patch
,(select (elem_count_histogram[array_length(elem_count_histogram,1)])
from pg_stats
where tablename = 'array_op_test' and attname = 'i')
as elem_count_histogram_last_element
from pg_class where relname = 'array_op_test';
original | with_patch | elem_count_histogram_last_element
----------+------------+-----------------------------------
1030 | 412 | 4.7843137
(1 row)
without patch:
explain select unnest(i) from array_op_test;
QUERY PLAN
----------------------------------------------------------------------
ProjectSet (cost=0.00..9.95 rows=1030 width=4)
-> Seq Scan on array_op_test (cost=0.00..4.03 rows=103 width=40)
(2 rows)
with patch:
explain select unnest(i) from array_op_test;
QUERY PLAN
----------------------------------------------------------------------
ProjectSet (cost=0.00..6.86 rows=412 width=4)
-> Seq Scan on array_op_test (cost=0.00..4.03 rows=103 width=40)
(2 rows)
--------
because, in the estimate_array_length function, `nelem =
sslot.numbers[sslot.nnumbers - 1];` will round 4.7843137 to 4.
so with patch estimated row 412 = 103 *4. without patch estimated rows
= 103 * 10.
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2023-11-27 07:08:08 | Re: Catalog domain not-null constraints |
Previous Message | Fujii.Yuki@df.MitsubishiElectric.co.jp | 2023-11-27 07:04:07 | RE: Partial aggregates pushdown |