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.
