Optimiser desicion bringing system to its knees?

From: Craig O'Shannessy <craig(at)ucw(dot)com(dot)au>
To: pgsql-general(at)postgresql(dot)org
Subject: Optimiser desicion bringing system to its knees?
Date: 2003-11-06 22:52:09
Message-ID: 3FAAD099.6050305@ucw.com.au
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi everyone,

My performance on a big mission critical system has recently collapsed,
and I've finally traced it down to the postgresql optimiser I think.
I'm running postgresql-7.2.1-2PGDG

The explains below make it clear I think. If I just change the table
declaration order, I get MASSIVELY better performance. I thought the
postgres optimiser was meant to make these desicions for me?

cop=# explain select sum(t1.quantity) from Shipment t2,
LineItem t1 where (t1.shipmentId=t2.shipmentId) AND ((((t1.sku =
'1614') AND (t1.status = 0)) AND t1.productReservationId is not null )
AND (t2.stage = 10));
NOTICE: QUERY PLAN:

Aggregate (cost=138079.92..138079.92 rows=1 width=20)
-> Nested Loop (cost=0.00..138079.91 rows=1 width=20)
-> Seq Scan on lineitem t1 (cost=0.00..138076.49 rows=1
width=12)
-> Index Scan using shipment_pkey on shipment t2
(cost=0.00..3.41 rows=1 width=8)

cop=# explain select sum(t1.quantity) from LineItem t1 ,
shipment t2 where (t1.shipmentId=t2.shipmentId) AND ((((t1.sku = '1614')
AND (t1.status = 0)) AND t1.productReservationId is not null ) AND
(t2.stage = 10));
NOTICE: QUERY PLAN:

Aggregate (cost=9.42..9.42 rows=1 width=20)
-> Nested Loop (cost=0.00..9.42 rows=1 width=20)
-> Index Scan using lineitem_sku_reservation_idx on lineitem
t1 (cost=0.00..6.00 rows=1 width=12)
-> Index Scan using shipment_pkey on shipment t2
(cost=0.00..3.41 rows=1 width=8)

NOTE : THE ONLY CHANGE ABOVE IS IN THE FROM CLAUSE.

Note that this is genereated SQL (from the MVCSoft CMP 2.0 EJB engine),
so unfortunately, I can't really do much about fixing it :((. If anyone
can tell me whether this is fixed or not already, I would be very grateful

TIA,

Craig

P.S. This is the second attempted delivery of this message.
subscribe-digest fails, so my first wasn't posted. If a duplicate
happens, I apologise.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bruce Momjian 2003-11-06 23:01:50 Re: PostgreSQL License Question
Previous Message Barbara Lindsey 2003-11-06 22:05:56 create function for trigger question