Re: Partition key causes problem for volatile target list query

On Thu, Jan 26, 2023 at 07:21:16PM -0500, Tom Lane wrote:
> Well, if you looked further than the first few rows, it wouldn't be
> "always zero". But the select from the partitioned table will read
> the first partition first, and that partition will have the rows
> with d1=0, by definition.
>
> =# explain select * from case_test2 limit 10;
> QUERY PLAN
>
> --------------------------------------------------------------------------------
> -----------
> Limit (cost=0.00..0.19 rows=10 width=8)
> -> Append (cost=0.00..1987.90 rows=102260 width=8)
> -> Seq Scan on case_test2_0 case_test2_1 (cost=0.00..478.84 rows=3318
> 4 width=8)
> -> Seq Scan on case_test2_1 case_test2_2 (cost=0.00..480.86 rows=3328
> 6 width=8)
> -> Seq Scan on case_test2_2 case_test2_3 (cost=0.00..484.30 rows=3353
> 0 width=8)
> -> Seq Scan on case_test2_3 case_test2_4 (cost=0.00..32.60 rows=2260
> width=8)
> (6 rows)
>
> The result appears sorted by d1, but that's an implementation artifact.

Wow, thanks. Not sure how I missed something so obvious. I just saw it
myself by generating only 10 rows and noticing the numbers were always
increasing.

--
Bruce Momjian <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Embrace your flaws. They make you human, rather than perfect,
which you will never be.