Hi John,

relpages is definitly not set by an optimizedb, it is set by a modify of the table.

The relpages is printed out by the statdump as the number of pages.

I tried a modify table to table_debug with table_option=2 and counted 116 pages (ie. a lot more than 40) flagged with either a D or an A. I also examined the tids in the table to check for page number and came up with 116 distinct values. The sequence of page numbers and D's and A's matches as well.

I don't know what an 'A' page is - they clearly have data on them - any ideas?

But of course the optimiser is a *LOT* smarter than I am. It realised that the query didn't rely on data just on keys, so it only need to scan the L pages holding the Key details to do the outer join. And guess what - theres 40 of them, and those are the pages that the lock trace show are being opened.

I suspect that my lock escalation is coming when sufficient activity on the table has screwed enough with the index of the btree table to cause an overflow. I'll start tracing the activity on the table leading up to the start of lock escalation and see what happens.

Marty

________________________________

From: info-ingres-bounces@kettleriverconsulting.com on behalf of John Dennis
Sent: Fri 21/09/2007 03:16
To: info-ingres@kettleriverconsulting.com
Subject: Re: [Info-Ingres] Lock escalation caused by bad page estimate

Marty,

As far as I can see for "normal" tables (not partitioned) although the
relpages attribute on iirelation is updated, it is updated to the
value it previously held. I just set relpages on a table to a dummy
value (1111), ran optimizedb, and the statdump showed the page count
to be 1111.

Are you able to run this again, printing off relpages from iirelation
before and after the optimizedb?

John

_______________________________________________
Info-Ingres mailing list
Info-Ingres@kettleriverconsulting.com
http://www.kettleriverconsulting.com/mailman/listinfo/info-ingres