Re: Optimiser desicion bringing system to its knees?

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


--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org