After upgrading to 7.0.2, one of my queries ran much slower on 7.0.2:
7.0.2 took 17 minutes (After 'optimization' by SET ENABLE_SEQSCAN=OFF)
6.5.3 took 2 minutes
The following is the query/explain under 6.5.3:
super=> select version();
version
-------------------------------------------------------------------
PostgreSQL 6.5.3 on i586-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)
explain select itm_section, itm_brand, itm_itemno, sum(stl_qty)
from stl, item where stl_itemno = itm_itemno
and stl_discounttype in ('','S','V')
and stl_trxdate>='Jul 01, 2000' and stl_trxdate<='Jul 02, 2000'
and stl_status='N'
and itm_sectiontype='O'
and itm_section >='101'
and itm_section <='135'
group by itm_section, itm_brand, itm_itemno;
NOTICE: QUERY PLAN:
Aggregate (cost=25780.13 rows=55386 width=72)
-> Group (cost=25780.13 rows=55386 width=72)
-> Sort (cost=25780.13 rows=55386 width=72)
-> Hash Join (cost=25780.13 rows=55386 width=72)
-> Index Scan using stl_datetrx on stl
(cost=15488.75
rows=210658 width=36)
-> Hash (cost=3225.66 rows=2879 width=36)
-> Seq Scan on item (cost=3225.66 rows=2879
width=36)
Under 7.0.2, there are no Hash Join or Hash
Are there any way to optimize this type of query? I have SET
ENABLE_SEQSCAN=OFF
and increased the buffer size. Without SEQSCAN=OFF the query took even
longer than
17 minutes.
Thanks,
Thomas.
