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 - [EMAIL PROTECTED] - http://www.commandprompt.com Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
