Обсуждение: Optimiser desicion bringing system to its knees?
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.
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
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 > > >
"Craig O'Shannessy" <craig@ucw.com.au> writes:
> The explains below make it clear I think. If I just change the table
> declaration order, I get MASSIVELY better performance.
Hm. Is the lineitem_sku_reservation_idx index a partial index, by any
chance? I see this bug fix in 7.2.2:
2002-08-22 12:20 tgl
* src/backend/optimizer/path/indxpath.c (REL7_2_STABLE): Back-patch
fix to make partial indexes usable on relations other than the
first one listed in a query. Per request from Oleg.
regards, tom lane
Ahh, this makes sense. I've recently changed this into a partial index because I was getting awful performance on it. Thanks Tom, this gives me a stopgap fix until I can get 7.4 into production. Craig On Thu, 6 Nov 2003, Tom Lane wrote: > "Craig O'Shannessy" <craig@ucw.com.au> writes: > > The explains below make it clear I think. If I just change the table > > declaration order, I get MASSIVELY better performance. > > Hm. Is the lineitem_sku_reservation_idx index a partial index, by any > chance? I see this bug fix in 7.2.2: > > 2002-08-22 12:20 tgl > > * src/backend/optimizer/path/indxpath.c (REL7_2_STABLE): Back-patch > fix to make partial indexes usable on relations other than the > first one listed in a query. Per request from Oleg. > > regards, tom lane >
In the last exciting episode, craig@ucw.com.au ("Craig O'Shannessy") wrote:
> 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 ;)
Would you rush FreeBSD 5.1 into production when they're still
experimenting with it?
Would you rush Linux 2.6 into production when they still haven't got
the .0 release of that?
I'm doing some active work testing 7.4RC1 with some apps on the basis
that by the time the software is ready to deploy, PG will surely have
reached version 7.4.0 or 7.4.1. But if I planned something for
production TOMORROW (e.g. - as in Friday, November 7, the day after
today, November 6th), I would be looking at 7.3.4.
Don't misinterpret that as any sort of "vote of nonconfidence;" it's
just the notion of having a modicum of conservatism, and staying a
_small_ step away from the bleeding edge of the abyss, at least when
looking at production systems.
I similarly haven't yet had opportunity to test out Jan Wieck's ARC
patch, improving the way the system copes with VACUUM when under heavy
load. Of vast ultimate interest though it may be, I simply haven't
got a test set ready to _properly_ torture the patch, so it makes
sense to wait and maybe look at it a little later as it matures.
There's an old line about rushing in where angels fear to tread.
Putting not-yet-released software into production is one of those
places...
--
(format nil "~S@~S" "cbbrowne" "ntlug.org")
http://www.ntlug.org/~cbbrowne/
Rules of the Evil Overlord #104. "My undercover agents will not have
tattoos identifying them as members of my organization, nor will they
be required to wear military boots or adhere to any other dress
codes." <http://www.eviloverlord.com/>