7.0.2 and 6.5.3 performance

From: lec <englim(at)pc(dot)jaring(dot)my>
To: pgsql-general(at)postgreSQL(dot)org
Subject: 7.0.2 and 6.5.3 performance
Date: 2000-09-15 15:59:50
Message-ID: 39C24776.3D4B62A@pc.jaring.my
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message John 2000-09-15 16:00:59 RE: are my questions being received?
Previous Message Scott Holdren 2000-09-15 15:54:09 test message