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.
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
