0

PostgreSQL seems to fail recognising the sorting is performed by constant and and each constant unambiguously defines corresponding row source, and as a result I observe it computes all row sources before "sorting":

create table t1 as
select generate_series(1, 10) as c1;


explain analyse
select c1
from (select 1, c1 from t1 where c1 = 1
      union all
      select 2, c1 from t1 where c1 = 2
      union all
      select 3, c1 from t1 where c1 = 3
      union all
      select 4, c1 from t1 where c1 = 4
      order by 1
      limit 1) q
Subquery Scan on q  (cost=169.84..169.85 rows=1 width=4) (actual time=0.501..0.685 rows=1 loops=1)
  ->  Limit  (cost=169.84..169.84 rows=1 width=8) (actual time=0.479..0.622 rows=1 loops=1)
        ->  Sort  (cost=169.84..169.97 rows=52 width=8) (actual time=0.458..0.570 rows=1 loops=1)
              Sort Key: (1)
              Sort Method: top-N heapsort  Memory: 25kB
              ->  Append  (cost=0.00..168.28 rows=52 width=8) (actual time=0.044..0.476 rows=4 loops=1)
                    ->  Seq Scan on t1  (cost=0.00..41.88 rows=13 width=8) (actual time=0.023..0.046 rows=1 loops=1)
                          Filter: (c1 = 1)
                          Rows Removed by Filter: 9
                    ->  Seq Scan on t1 t1_1  (cost=0.00..41.88 rows=13 width=8) (actual time=0.016..0.038 rows=1 loops=1)
                          Filter: (c1 = 2)
                          Rows Removed by Filter: 9
                    ->  Seq Scan on t1 t1_2  (cost=0.00..41.88 rows=13 width=8) (actual time=0.016..0.038 rows=1 loops=1)
                          Filter: (c1 = 3)
                          Rows Removed by Filter: 9
                    ->  Seq Scan on t1 t1_3  (cost=0.00..41.88 rows=13 width=8) (actual time=0.017..0.039 rows=1 loops=1)
                          Filter: (c1 = 4)
                          Rows Removed by Filter: 9

However, after removing order by 1 I'm getting exactly what I want to get - never executed notices in query plan:

explain analyse
select c1
from (select 1, c1 from t1 where c1 = 1
      union all
      select 2, c1 from t1 where c1 = 2
      union all
      select 3, c1 from t1 where c1 = 3
      union all
      select 4, c1 from t1 where c1 = 4
      --order by 1
      limit 1) q
Subquery Scan on q  (cost=0.00..3.25 rows=1 width=4) (actual time=0.092..0.195 rows=1 loops=1)
  ->  Limit  (cost=0.00..3.24 rows=1 width=8) (actual time=0.069..0.131 rows=1 loops=1)
        ->  Append  (cost=0.00..168.28 rows=52 width=8) (actual time=0.047..0.078 rows=1 loops=1)
              ->  Seq Scan on t1  (cost=0.00..41.88 rows=13 width=8) (actual time=0.026..0.037 rows=1 loops=1)
                    Filter: (c1 = 1)
              ->  Seq Scan on t1 t1_1  (cost=0.00..41.88 rows=13 width=8) (never executed)
                    Filter: (c1 = 2)
              ->  Seq Scan on t1 t1_2  (cost=0.00..41.88 rows=13 width=8) (never executed)
                    Filter: (c1 = 3)
              ->  Seq Scan on t1 t1_3  (cost=0.00..41.88 rows=13 width=8) (never executed)
                    Filter: (c1 = 4)

The question is: is it safe to omit order by in such cases?

1 Answer 1

1

The optimizer isn't smart enough to figure out that the rows are already sorted. so it sorts again. For that, it needs all rows. Simply omit the ORDER BY.

2
  • thanks, what does prevent optimizer from permuting row sources defined in union all? Commented Jun 9, 2023 at 15:14
  • That nobody implemented it. I doubt that we would be interested in such a change: it is a very narrow corner case, and it would require extra processing time in the optimizer for every UNION ALL query. Commented Jun 9, 2023 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.