Optimiser desicion bringing system to its knees?

[Craig O'Shannessy]

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

[Joshua D. Drake]

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.
[phone] - [e-mail] - 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 ma*******@postgresql.org)

[Craig O'Shannessy]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Tom Lane]

"Craig O'Shannessy" <cr***@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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Craig O'Shannessy]

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" <cr***@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Christopher Browne]

In the last exciting episode, cr***@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/>