The following bug has been logged online:
Bug reference: 1878
Logged by: Alexei
Email address: alexei_novakov@yahoo.com
PostgreSQL version: 8.0
Operating system: Windows 2000
Description: Different execution plans for the same query.
Details:
I was doing performance evaluation of various DB servers including
PostgreSQL. For one of test queries I got strange result - timing was
different (from 0.5 to 240 sec) even if I was running it one time after
another without going anything in between (like there were no inserts or
whatever similar). I did vacuum and vacuum analyse, but it didn't change
anything. I checked query plan and found that this plan is different every
time. Sometimes it generates plan which uses indices all the way, but
sometimes it goes for table (or even two tables) scan. Although the result
of the query always was correct whichever plan it's using. I repete - I did
not do anything between queries, but just ran the same 'explain select ...'
sequentially. The query itself is heavy, but pretty strightforward - 2
tables joined 12 times. Here it is, just in case:
select T1.MAIN_ID, T2.ORDER_F, T3.ORDER_F,
T4.ORDER_F, T5.ORDER_F, T6.LEVEL_IND,
T6.PRE_IND, T7.ORDER_F, T8.ORDER_F, T9.ORDER_F,
T10.ORDER_F, T11.ORDER_F, T12.MAIN_ID
from TMP_MAIN T1, TMP_RS T2, TMP_RS T3, TMP_RS T4,
TMP_RS T5, RSCACHE_R T6, TMP_RS T7, TMP_RS T8,
TMP_RS T9, TMP_RS T10, TMP_RS T11, TMP_MAIN T12
where T2.ID_2 = T1.MAIN_ID
and T2.ID_TYPE = 269
and 990765472621084681 = T2.ID_1
and T4.ID_1 = T12.MAIN_ID
and T4.ID_TYPE = 143
and T3.ID_TYPE = 143
and 990765472621084681 = T3.ID_1
and T4.ID_2 = T3.ID_2
and T7.ID_1 = T12.MAIN_ID
and T7.ID_TYPE = 224
and T6.ID_TYPE = 209
and T6.LEVEL_IND != 0
and T5.ID_TYPE = 224
and 990765472621084681 = T5.ID_1
and T6.ID_1 = T5.ID_2
and T7.ID_2 = T6.ID_2
and T9.ID_1 = T12.MAIN_ID
and T9.ID_TYPE = 125
and T8.ID_TYPE = 125
and 990765472621084681 = T8.ID_1
and T9.ID_2 = T8.ID_2
and T11.ID_1 = T12.MAIN_ID
and T11.ID_TYPE = 71
and T10.ID_TYPE = 71
and 990765472621084681 = T10.ID_1
and T11.ID_2 = T10.ID_2
and T12.ID_TYPE = 286
Both tables have 500000-1000000 records. If you need more information
contact me via e-mail.
Regards.
Alexei.
