Page 1 of 2
Is it possible improve this statament?
Posted: Tue Jul 05, 2016 2:03 pm
by 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.
Re: Is it possible improve this statament?
Posted: Tue Jul 05, 2016 2:16 pm
by 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 ;
Re: Is it possible improve this statament?
Posted: Tue Jul 05, 2016 2:19 pm
by 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
Re: Is it possible improve this statament?
Posted: Tue Jul 05, 2016 2:24 pm
by paulomhouse
Thanks. I will verify.
Re: Is it possible improve this statament?
Posted: Tue Jul 05, 2016 2:34 pm
by paulomhouse
I've tried where r.status='A' and r.qtde > t ; but return "column "t" does not exist"
Not index yet.
Re: Is it possible improve this statament?
Posted: Tue Jul 05, 2016 2:56 pm
by paulomhouse
performance improve to much with index
Re: Is it possible improve this statament?
Posted: Tue Jul 05, 2016 3:02 pm
by Rathinagiri
If you can give some sample data in .sql format I can verify in SQLite.
Re: Is it possible improve this statament?
Posted: Tue Jul 05, 2016 3:56 pm
by 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)"
Re: Is it possible improve this statament?
Posted: Tue Jul 05, 2016 4:09 pm
by paulomhouse
Thanks a bunch Rathinagiri
Re: Is it possible improve this statament?
Posted: Tue Jul 05, 2016 4:12 pm
by 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 ;