Is it possible improve this statament?

[paulomhouse]

Hi Everyone.

I have a statement select above what is working. But very slowly.

Database: Postgresql

Can you help to improve?

I think the problem is on necessity for check Subquery(RED color) in Where.

select r.codigo,r.cod,descr,r.qtde,(select sum(qtde) from a19item group by req_cod having req_cod = r.codigo) as t
from a19req r left join titemg i on r.cod = i.codigo
where r.status='A' and r.qtde-(select sum(qtde) as qtsai from a19item t group by req_cod having req_cod = r.codigo) > 0 ;


Many thank you.

[Rathinagiri]

Hi,

Can you tell me whether the table a19item is indexed by the column req_cod?

Can't you use this?

Code: Select all

select r.codigo,r.cod,descr,r.qtde,(select sum(qtde) from a19item group by req_cod having req_cod = r.codigo) as t
from a19req r left join titemg i on r.cod = i.codigo
where r.status='A' and r.qtde > t ;

or

Code: Select all

select r.codigo,r.cod,descr,r.qtde,(select sum(qtde) from a19item where req_cod = r.codigo) as t
from a19req r left join titemg i on r.cod = i.codigo
where r.status='A' and r.qtde > t ;

[Rathinagiri]

Please use Explain statement to know about the query plan. Can you share your result of Explain statement as shown here? https://www.postgresql.org/docs/9.2/sta ... plain.html

[paulomhouse]

Thanks. I will verify.

[paulomhouse]

I've tried where r.status='A' and r.qtde > t ; but return "column "t" does not exist"


Not index yet.

[paulomhouse]

performance improve to much with index

[Rathinagiri]

If you can give some sample data in .sql format I can verify in SQLite.

[paulomhouse]

result of Explain:

"Hash Left Join (cost=367.11..224934.79 rows=6716 width=62)"
" Hash Cond: ((r.cod)::text = (i.codigo)::text)"
" -> Seq Scan on a19req r (cost=0.00..168556.25 rows=6716 width=28)"
" Filter: ((qtde - (SubPlan 2)) > 0::double precision)"
" SubPlan 2"
" -> GroupAggregate (cost=0.29..8.32 rows=1 width=12)"
" -> Index Scan using a19item_id_req_cod on a19item t (cost=0.29..8.30 rows=1 width=12)"
" Index Cond: (req_cod = r.codigo)"
" -> Hash (cost=284.27..284.27 rows=6627 width=48)"
" -> Seq Scan on titemg i (cost=0.00..284.27 rows=6627 width=48)"
" SubPlan 1"
" -> GroupAggregate (cost=0.29..8.32 rows=1 width=12)"
" -> Index Scan using a19item_id_req_cod on a19item (cost=0.29..8.30 rows=1 width=12)"
" Index Cond: (req_cod = r.codigo)"

[paulomhouse]

Thanks a bunch Rathinagiri

[Rathinagiri]

Hi,

In this query why do you have the left join?

How about this?

select r.codigo,r.cod,descr,r.qtde,(select sum(qtde) from a19item where req_cod = r.codigo) as t
from a19req r where r.status='A' and r.qtde-(select sum(qtde) from a19item t where req_cod = r.codigo) > 0 ;