Hi, I'm not onsite, but I just heard back from a colleage that rushed in to try exactly that. It does indeed seem to be fixed in 7.4RC1. I assume this will be pretty stable, and there seems to be heaps of enhancements, so I think I'll just skip 7.3.x. I'm not doing anything funky with postgreSQL, but if someone has a good reason for me NOT putting 7.4RC1 onto a production system, please let me know ;)
Thanks for the reply, Joshua. Craig On Thu, 6 Nov 2003, Joshua D. Drake wrote: > Hello, > > Well the first thing I would ask is what does the optimizer do if you > are running a current version of PostgreSQL? Specifically either > 7.3.4 or even 7.4RC1. > > Sincerely, > > Joshua Drake > > > Craig O'Shannessy wrote: > > > 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. > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
