Postgresql – SQL Statements truncated in log files

Your additional calculated value:

(select count(t1.num) from T t1)

Is a scalar subquery, which is a dynamic rather than static expression. As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function error

However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:

group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

just results in an ORA-22818: subquery expressions not allowed here error.

The only ways around this are to either convert your scalar subquery to an aggregate value like so:

max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

or

(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)

or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:

select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
  from (select sum(t3.num) sum from t t3) t2;

or precompute your scalar subquery so it can be used in the group by clause:

select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
  from t t2
     , (select count(*) cnt from t) t1
 group by case t1.cnt when 0 then 1 else 0 end

You have two Seq Scans in that query.

1. Over dim_order_item_status.is_reserved
2. Another one over foi.fk_dim_order_item_status

Do either of these have indexes? If not, create the index and VACUUM ANALYZE the table.

A few other notes,

1. In PostgreSQL we don't use 1 for boolean.

   ALTER TABLE dim_order_item_status
     ALTER COLUMN is_reserved SET DATA TYPE bool;
   

2. Also count(1) is better written as count(*)

As for why the second query is faster, the concatenation is slowing it down so it pushes the index scan further down. My assumption is that your statistics are off for the rows returned by fk_dim_order_item_status = dois.id_dim_order_item_status. Normally, we could verify this if you ran EXPLAIN ANALYZE rather than just EXPLAIN. But anyway, run either VACUUM ANALYZE or ANALYZE on those tables if the problem persists after you create the indexes above.

If you come back we need:

1. VACUUM ANALYZE for all queries.
2. \d dim_order_item_status
3. \d dim_order_item_foi