Re: Optimiser desicion bringing system to its knees?

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