Re: 7.0.2 and 6.5.3 performance

Tom Lane wrote:

> lec <englim(at)pc(dot)jaring(dot)my> writes:
> > Under 7.0.2, there are no Hash Join or Hash
>
> So, then, what *does* it do? Without the explain for 7.0.2 it's
> impossible to tell what's happening.

========================================================
The following is the query/explain under 7.0.2:

super=> select version();
version
---------------------------------------------------------------------
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66
(1 row)

set enable_seqscan=on;

psql:explainsum.sql:48: NOTICE: QUERY PLAN:

Aggregate (cost=27770.89..27770.90 rows=0 width=72)
-> Group (cost=27770.89..27770.89 rows=1 width=72)
-> Sort (cost=27770.89..27770.89 rows=1 width=72)
-> Nested Loop (cost=0.00..27770.88 rows=1 width=72)
-> Seq Scan on item (cost=0.00..2549.45 rows=1
width=36)
-> Index Scan using stl_datetrx on stl
(cost=0.00..25157.34 rows=5127 width=36)

set enable_seqscan=off;

psql:explainsum.sql:62: NOTICE: QUERY PLAN:

Aggregate (cost=42806.95..42806.96 rows=0 width=72)
-> Group (cost=42806.95..42806.96 rows=1 width=72)
-> Sort (cost=42806.95..42806.95 rows=1 width=72)
-> Nested Loop (cost=0.00..42806.94 rows=1 width=72)
-> Index Scan using item_pkey on item
(cost=0.00..17585.52 rows=1 width=36)
-> Index Scan using stl_datetrx on stl
(cost=0.00..25157.34 rows=5127 width=36)

========================================================
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)

>
>
> > Are there any way to optimize this type of query?
>
> Um, have you vacuum analyzed the tables involved?

The tables were vacuumed before explain.

The time required for 7.0.2 is exponential to the amount of data being
queried whereas for 6.5.3, it is linear to the amount of data.

Thanks & regards,
Thomas.