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(at)commandprompt(dot)com - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org