Re: 7.2.1 optimises very badly against 7.2

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: 08 July 2002 16:22
> To: Sam Liddicott
> Cc: pgsql-general(at)postgresql(dot)org
> Subject: Re: [GENERAL] 7.2.1 optimises very badly against 7.2
>
>
> "Sam Liddicott" <sam(dot)liddicott(at)ananova(dot)com> writes:
> > O notice missing in the 7.2.1 (slow) explain analyse this part:
> > "Index Scan using idx_broadcast_channelregionid on broadcast"
>
> Indeed. What do 7.2 and 7.2.1 have in the pg_stats row for
> broadcast.channelregionid? What is the real distribution of that
> column?

7.2 says:
tv=# select * from pg_stats where attname='channelregionid';
tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals |
most_common_freqs |
histogram_bounds | correlation
-----------+-----------------+-----------+-----------+------------+---------
----------------------+-----------------------------------------------------
----------------------------------+-----------------------------------------
+-------------
broadcast | channelregionid | 0 | 4 | 4532 |
{54,81,2,22,1,4,645,76,53,23} |
{0.0376667,0.0346667,0.0293333,0.029,0.0273333,0.024,0.0236667,0.019,0.01866
67,0.018} | {3,16,36,49,90,170,231,425,494,659,747} | -0.155299
(1 row)

................
channelregionid | count
-----------------+-------
418 | 11698
588 | 8677
417 | 8331
138 | 7435
170 | 7336
219 | 6729
701 | 6585
184 | 6584
218 | 6537
109 | 6479
195 | 6367
734 | 6341
67 | 6235
33 | 5902
615 | 5900
707 | 5899
136 | 5896
227 | 5806
49 | 5754
414 | 5714
1 | 5710
122 | 5646
413 | 5629
48 | 5603
2 | 5593
415 | 5586
3 | 5581
34 | 5579
186 | 5565
13 | 5547
15 | 5546
11 | 5545
12 | 5545
18 | 5545
713 | 5545
9 | 5544
14 | 5544
4 | 5542
10 | 5542
17 | 5541
412 | 5541
16 | 5539
92 | 5493
39 | 5489
35 | 5393
612 | 5371
99 | 5346
678 | 5333
659 | 5304
45 | 5287
46 | 5287
85 | 5286
102 | 5269
705 | 5267
215 | 5252
190 | 5249
709 | 5247
47 | 5234
44 | 5221
36 | 5216
194 | 5210
38 | 5188
698 | 5187
661 | 5136
37 | 5134
40 | 5128
41 | 5114
663 | 5081
82 | 5068
42 | 5051
19 | 5036
81 | 5022
95 | 5019
141 | 4996
54 | 4984
52 | 4980
20 | 4979
25 | 4978
27 | 4978
24 | 4977
29 | 4977
31 | 4977
724 | 4977
22 | 4976
23 | 4976
26 | 4976
660 | 4976
30 | 4975
32 | 4975
420 | 4975
185 | 4966
43 | 4958
21 | 4933
149 | 4756
53 | 4692
480 | 4663
76 | 4652
91 | 4606
134 | 4577
89 | 4536
168 | 4507
700 | 4506
487 | 4499
200 | 4381
222 | 4379
617 | 4329
71 | 4250
613 | 4199
83 | 4198
128 | 4127
130 | 4076
188 | 4070
703 | 4060
197 | 4042
169 | 4025
706 | 4018
129 | 3972
66 | 3944
112 | 3851
704 | 3849
641 | 3809
232 | 3708
622 | 3696
133 | 3695
110 | 3649
221 | 3549
645 | 3484
183 | 3441
634 | 3404
738 | 3399
682 | 3376
123 | 3352
166 | 3346
90 | 3283
64 | 3258
84 | 3248
58 | 3237
631 | 3214
636 | 3180
635 | 3179
639 | 3176
640 | 3176
177 | 3144
710 | 3142
478 | 3124
203 | 3121
172 | 3066
733 | 3061
192 | 3051
214 | 3051
633 | 2962
632 | 2923
722 | 2909
171 | 2698
702 | 2695
107 | 2685
161 | 2658
485 | 2622
696 | 2598
638 | 2568
474 | 2559
275 | 2549
274 | 2546
451 | 2489
637 | 2486
619 | 2470
155 | 2406
433 | 2373
216 | 2334
431 | 2329
231 | 2279
241 | 2261
63 | 2253
605 | 2233
150 | 2227
114 | 2091
223 | 2048
606 | 2047
139 | 2036
73 | 2031
120 | 2020
668 | 2020
96 | 1984
68 | 1977
657 | 1976
365 | 1949
608 | 1940
368 | 1900
8 | 1888
187 | 1864
86 | 1830
70 | 1817
50 | 1813
175 | 1808
124 | 1806
69 | 1802
367 | 1801
119 | 1795
144 | 1791
178 | 1774
125 | 1753
174 | 1728
143 | 1724
74 | 1680
278 | 1666
422 | 1659
379 | 1644
369 | 1595
313 | 1594
535 | 1594
261 | 1553
154 | 1552
435 | 1523
359 | 1505
308 | 1495
530 | 1494
534 | 1493
543 | 1490
542 | 1487
111 | 1481
461 | 1481
249 | 1476
620 | 1476
602 | 1475
614 | 1469
153 | 1463
228 | 1459
87 | 1457
536 | 1451
289 | 1434
601 | 1421
524 | 1418
525 | 1418
512 | 1383
513 | 1383
375 | 1373
560 | 1373
518 | 1372
245 | 1370
521 | 1369
495 | 1367
493 | 1366
494 | 1366
454 | 1364
561 | 1364
505 | 1363
56 | 1358
496 | 1358
544 | 1356
284 | 1353
77 | 1349
78 | 1348
79 | 1348
80 | 1348
545 | 1347
540 | 1342
541 | 1342
537 | 1337
358 | 1334
618 | 1310
556 | 1299
557 | 1299
349 | 1290
366 | 1282
712 | 1280
425 | 1279
528 | 1276
529 | 1276
572 | 1276
568 | 1272
508 | 1271
509 | 1271
514 | 1257
727 | 1257
515 | 1256
362 | 1255
396 | 1254
603 | 1254
342 | 1249
479 | 1248
486 | 1248
554 | 1247
564 | 1246
565 | 1246
394 | 1239
229 | 1237
582 | 1232
570 | 1230
571 | 1230
292 | 1227
321 | 1227
286 | 1222
287 | 1222
510 | 1222
511 | 1222
580 | 1220
266 | 1218
531 | 1217
716 | 1213
546 | 1211
547 | 1211
491 | 1210
492 | 1210
374 | 1203
472 | 1203
563 | 1203
462 | 1199
500 | 1194
584 | 1193
499 | 1188
562 | 1188
731 | 1185
742 | 1184
437 | 1182
555 | 1182
280 | 1172
720 | 1170
581 | 1168
717 | 1168
732 | 1162
432 | 1160
242 | 1156
548 | 1151
549 | 1151
579 | 1151
260 | 1150
567 | 1149
569 | 1149
578 | 1148
428 | 1147
532 | 1146
559 | 1145
438 | 1144
621 | 1143
371 | 1138
333 | 1137
522 | 1137
533 | 1135
523 | 1132
558 | 1132
538 | 1126
539 | 1126
489 | 1124
714 | 1121
427 | 1115
506 | 1114
735 | 1107
59 | 1104
517 | 1104
430 | 1101
255 | 1099
336 | 1087
516 | 1084
652 | 1077
383 | 1076
387 | 1072
285 | 1071
251 | 1069
699 | 1069
322 | 1067
552 | 1067
553 | 1067
309 | 1063
473 | 1061
550 | 1061
551 | 1061
497 | 1060
498 | 1060
697 | 1060
385 | 1056
470 | 1046
256 | 1044
282 | 1044
296 | 1044
299 | 1044
314 | 1044
456 | 1044
650 | 1044
381 | 1042
463 | 1040
477 | 1040
335 | 1036
402 | 1036
471 | 1034
55 | 1033
646 | 1033
360 | 1031
643 | 1031
653 | 1031
279 | 1026
320 | 1026
352 | 1023
331 | 1021
364 | 1020
356 | 1018
465 | 1016
574 | 1016
464 | 1014
673 | 1014
103 | 1013
234 | 1013
334 | 1013
380 | 1013
295 | 1008
332 | 1006
263 | 1004
482 | 1004
585 | 1004
353 | 1000
361 | 998
401 | 997
484 | 996
294 | 993
217 | 987
156 | 986
246 | 986
312 | 986
311 | 985
376 | 984
399 | 984
377 | 983
594 | 982
330 | 981
692 | 978
271 | 977
267 | 976
270 | 976
272 | 976
277 | 976
326 | 975
575 | 972
233 | 966
298 | 966
305 | 966
424 | 966
649 | 966
235 | 965
459 | 963
526 | 962
372 | 960
408 | 959
527 | 954
319 | 947
599 | 947
651 | 947
340 | 945
373 | 945
577 | 945
403 | 944
469 | 943
327 | 938
455 | 937
719 | 936
158 | 931
236 | 926
258 | 926
276 | 926
488 | 926
586 | 926
476 | 925
388 | 924
501 | 924
502 | 924
409 | 919
573 | 918
744 | 917
264 | 906
445 | 904
443 | 903
283 | 902
442 | 902
444 | 900
407 | 896
339 | 890
252 | 889
247 | 888
503 | 888
253 | 886
273 | 886
589 | 886
583 | 884
576 | 882
239 | 880
607 | 877
406 | 876
220 | 875
386 | 873
440 | 872
329 | 871
384 | 871
350 | 870
405 | 870
708 | 870
250 | 868
604 | 868
392 | 864
429 | 864
140 | 862
248 | 859
458 | 858
393 | 854
439 | 852
357 | 850
595 | 850
262 | 849
269 | 848
304 | 848
318 | 848
647 | 848
723 | 848
354 | 847
268 | 846
281 | 846
648 | 846
240 | 845
225 | 842
325 | 839
224 | 838
146 | 836
441 | 833
389 | 832
664 | 832
145 | 829
481 | 829
315 | 828
644 | 827
346 | 825
328 | 823
404 | 812
475 | 812
348 | 810
378 | 809
600 | 805
390 | 792
466 | 792
747 | 792
341 | 790
426 | 790
593 | 789
316 | 788
301 | 773
457 | 772
591 | 772
592 | 772
288 | 770
587 | 758
597 | 758
460 | 752
590 | 752
291 | 745
436 | 739
254 | 731
683 | 731
244 | 730
715 | 721
324 | 715
721 | 708
297 | 690
654 | 690
310 | 673
338 | 672
382 | 641
237 | 632
693 | 632
448 | 629
355 | 600
370 | 600
259 | 592
118 | 588
238 | 572
351 | 494
395 | 456
290 | 454
265 | 453
300 | 434
655 | 424
656 | 417
303 | 414
323 | 410
446 | 399
179 | 366
293 | 363
658 | 360
363 | 351
230 | 338
756 | 337
642 | 336
116 | 332
691 | 306
307 | 296
317 | 296
306 | 276
257 | 215
159 | 168
181 | 163
180 | 160
737 | 144
60 | 138
62 | 138
93 | 138
100 | 138
101 | 138
104 | 138
151 | 138
204 | 138
205 | 138
206 | 138
207 | 138
208 | 138
209 | 138
210 | 138
211 | 138
212 | 138
213 | 138
410 | 138
411 | 138
616 | 138
121 | 137
749 | 136
182 | 135
337 | 135
596 | 135
450 | 134
189 | 131
449 | 119
447 | 114
751 | 90
142 | 81
745 | 67
743 | 66
711 | 56
391 | 42
694 | 24
137 | 19
695 | 13
736 | 11
(636 rows)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

7.2.1 says:
tv=# select * from pg_stats where attname='channelregionid';
tablename | attname | null_frac | avg_width | n_distinct |
most_common_vals | most_common_freqs
| histogram_bounds | correlation
-----------+-----------------+-----------+-----------+------------+---------
---------------------+------------------------------------------------------
---------------------------------+-----------------------------------------+
-------------
broadcast | channelregionid | 0 | 4 | 429 |
{2,54,76,4,81,1,37,3,22,487} |
{0.0326667,0.032,0.029,0.028,0.0273333,0.0253333,0.0253333,0.0233333,0.02266
67,0.021} | {8,24,38,64,91,183,250,428,567,659,756} | -0.19478
(1 row)

..........
channelregionid | count
-----------------+-------
418 | 11698
588 | 8677
417 | 8331
138 | 7435
170 | 7336
219 | 6729
701 | 6585
184 | 6584
218 | 6537
109 | 6479
195 | 6367
734 | 6341
67 | 6235
33 | 5902
615 | 5900
707 | 5899
136 | 5896
227 | 5806
49 | 5754
414 | 5714
1 | 5710
122 | 5646
413 | 5629
48 | 5603
2 | 5593
415 | 5586
3 | 5581
34 | 5579
186 | 5565
13 | 5547
15 | 5546
11 | 5545
12 | 5545
18 | 5545
713 | 5545
9 | 5544
14 | 5544
4 | 5542
10 | 5542
17 | 5541
412 | 5541
16 | 5539
92 | 5493
39 | 5489
35 | 5393
612 | 5371
99 | 5346
678 | 5333
659 | 5304
45 | 5287
46 | 5287
85 | 5286
102 | 5269
705 | 5267
215 | 5252
190 | 5249
709 | 5247
47 | 5234
44 | 5221
36 | 5216
194 | 5210
38 | 5188
698 | 5187
661 | 5136
37 | 5134
40 | 5128
41 | 5114
663 | 5081
82 | 5068
42 | 5051
19 | 5036
81 | 5022
95 | 5019
141 | 4996
54 | 4984
52 | 4980
20 | 4979
25 | 4978
27 | 4978
24 | 4977
29 | 4977
31 | 4977
724 | 4977
22 | 4976
23 | 4976
26 | 4976
660 | 4976
30 | 4975
32 | 4975
420 | 4975
185 | 4966
43 | 4958
21 | 4933
149 | 4756
53 | 4692
480 | 4663
76 | 4652
91 | 4606
134 | 4577
89 | 4536
168 | 4507
700 | 4506
487 | 4499
200 | 4381
222 | 4379
617 | 4329
71 | 4250
613 | 4199
83 | 4198
128 | 4127
130 | 4076
188 | 4070
703 | 4060
197 | 4042
169 | 4025
706 | 4018
129 | 3972
66 | 3944
112 | 3851
704 | 3849
641 | 3809
232 | 3708
622 | 3696
133 | 3695
110 | 3649
221 | 3549
645 | 3484
183 | 3441
634 | 3404
738 | 3399
682 | 3376
123 | 3352
166 | 3346
90 | 3283
64 | 3258
84 | 3248
58 | 3237
631 | 3214
636 | 3180
635 | 3179
639 | 3176
640 | 3176
177 | 3144
710 | 3142
478 | 3124
203 | 3121
172 | 3066
733 | 3061
192 | 3051
214 | 3051
633 | 2962
632 | 2923
722 | 2909
171 | 2698
702 | 2695
107 | 2685
161 | 2658
485 | 2622
696 | 2598
638 | 2568
474 | 2559
275 | 2549
274 | 2546
451 | 2489
637 | 2486
619 | 2470
155 | 2406
433 | 2373
216 | 2334
431 | 2329
231 | 2279
241 | 2261
63 | 2253
605 | 2233
150 | 2227
114 | 2091
223 | 2048
606 | 2047
139 | 2036
73 | 2031
120 | 2020
668 | 2020
96 | 1984
68 | 1977
657 | 1976
365 | 1949
608 | 1940
368 | 1900
8 | 1888
187 | 1864
86 | 1830
70 | 1817
50 | 1813
175 | 1808
124 | 1806
69 | 1802
367 | 1801
119 | 1795
144 | 1791
178 | 1774
125 | 1753
174 | 1728
143 | 1724
74 | 1680
278 | 1666
422 | 1659
379 | 1644
369 | 1595
313 | 1594
535 | 1594
261 | 1553
154 | 1552
435 | 1523
359 | 1505
308 | 1495
530 | 1494
534 | 1493
543 | 1490
542 | 1487
111 | 1481
461 | 1481
249 | 1476
620 | 1476
602 | 1475
614 | 1469
153 | 1463
228 | 1459
87 | 1457
536 | 1451
289 | 1434
601 | 1421
524 | 1418
525 | 1418
512 | 1383
513 | 1383
375 | 1373
560 | 1373
518 | 1372
245 | 1370
521 | 1369
495 | 1367
493 | 1366
494 | 1366
454 | 1364
561 | 1364
505 | 1363
56 | 1358
496 | 1358
544 | 1356
284 | 1353
77 | 1349
78 | 1348
79 | 1348
80 | 1348
545 | 1347
540 | 1342
541 | 1342
537 | 1337
358 | 1334
618 | 1310
556 | 1299
557 | 1299
349 | 1290
366 | 1282
712 | 1280
425 | 1279
528 | 1276
529 | 1276
572 | 1276
568 | 1272
508 | 1271
509 | 1271
514 | 1257
727 | 1257
515 | 1256
362 | 1255
396 | 1254
603 | 1254
342 | 1249
479 | 1248
486 | 1248
554 | 1247
564 | 1246
565 | 1246
394 | 1239
229 | 1237
582 | 1232
570 | 1230
571 | 1230
292 | 1227
321 | 1227
286 | 1222
287 | 1222
510 | 1222
511 | 1222
580 | 1220
266 | 1218
531 | 1217
716 | 1213
546 | 1211
547 | 1211
491 | 1210
492 | 1210
374 | 1203
472 | 1203
563 | 1203
462 | 1199
500 | 1194
584 | 1193
499 | 1188
562 | 1188
731 | 1185
742 | 1184
437 | 1182
555 | 1182
280 | 1172
720 | 1170
581 | 1168
717 | 1168
732 | 1162
432 | 1160
242 | 1156
548 | 1151
549 | 1151
579 | 1151
260 | 1150
567 | 1149
569 | 1149
578 | 1148
428 | 1147
532 | 1146
559 | 1145
438 | 1144
621 | 1143
371 | 1138
333 | 1137
522 | 1137
533 | 1135
523 | 1132
558 | 1132
538 | 1126
539 | 1126
489 | 1124
714 | 1121
427 | 1115
506 | 1114
735 | 1107
59 | 1104
517 | 1104
430 | 1101
255 | 1099
336 | 1087
516 | 1084
652 | 1077
383 | 1076
387 | 1072
285 | 1071
251 | 1069
699 | 1069
322 | 1067
552 | 1067
553 | 1067
309 | 1063
473 | 1061
550 | 1061
551 | 1061
497 | 1060
498 | 1060
697 | 1060
385 | 1056
470 | 1046
256 | 1044
282 | 1044
296 | 1044
299 | 1044
314 | 1044
456 | 1044
650 | 1044
381 | 1042
463 | 1040
477 | 1040
335 | 1036
402 | 1036
471 | 1034
55 | 1033
646 | 1033
360 | 1031
643 | 1031
653 | 1031
279 | 1026
320 | 1026
352 | 1023
331 | 1021
364 | 1020
356 | 1018
465 | 1016
574 | 1016
464 | 1014
673 | 1014
103 | 1013
234 | 1013
334 | 1013
380 | 1013
295 | 1008
332 | 1006
263 | 1004
482 | 1004
585 | 1004
353 | 1000
361 | 998
401 | 997
484 | 996
294 | 993
217 | 987
156 | 986
246 | 986
312 | 986
311 | 985
376 | 984
399 | 984
377 | 983
594 | 982
330 | 981
692 | 978
271 | 977
267 | 976
270 | 976
272 | 976
277 | 976
326 | 975
575 | 972
233 | 966
298 | 966
305 | 966
424 | 966
649 | 966
235 | 965
459 | 963
526 | 962
372 | 960
408 | 959
527 | 954
319 | 947
599 | 947
651 | 947
340 | 945
373 | 945
577 | 945
403 | 944
469 | 943
327 | 938
455 | 937
719 | 936
158 | 931
236 | 926
258 | 926
276 | 926
488 | 926
586 | 926
476 | 925
388 | 924
501 | 924
502 | 924
409 | 919
573 | 918
744 | 917
264 | 906
445 | 904
443 | 903
283 | 902
442 | 902
444 | 900
407 | 896
339 | 890
252 | 889
247 | 888
503 | 888
253 | 886
273 | 886
589 | 886
583 | 884
576 | 882
239 | 880
607 | 877
406 | 876
220 | 875
386 | 873
440 | 872
329 | 871
384 | 871
350 | 870
405 | 870
708 | 870
250 | 868
604 | 868
392 | 864
429 | 864
140 | 862
248 | 859
458 | 858
393 | 854
439 | 852
357 | 850
595 | 850
262 | 849
269 | 848
304 | 848
318 | 848
647 | 848
723 | 848
354 | 847
268 | 846
281 | 846
648 | 846
240 | 845
225 | 842
325 | 839
224 | 838
146 | 836
441 | 833
389 | 832
664 | 832
145 | 829
481 | 829
315 | 828
644 | 827
346 | 825
328 | 823
404 | 812
475 | 812
348 | 810
378 | 809
600 | 805
390 | 792
466 | 792
747 | 792
341 | 790
426 | 790
593 | 789
316 | 788
301 | 773
457 | 772
591 | 772
592 | 772
288 | 770
587 | 758
597 | 758
460 | 752
590 | 752
291 | 745
436 | 739
254 | 731
683 | 731
244 | 730
715 | 721
324 | 715
721 | 708
297 | 690
654 | 690
310 | 673
338 | 672
382 | 641
237 | 632
693 | 632
448 | 629
355 | 600
370 | 600
259 | 592
118 | 588
238 | 572
351 | 494
395 | 456
290 | 454
265 | 453
300 | 434
655 | 424
656 | 417
303 | 414
323 | 410
446 | 399
179 | 366
293 | 363
658 | 360
363 | 351
230 | 338
756 | 337
642 | 336
116 | 332
691 | 306
307 | 296
317 | 296
306 | 276
257 | 215
159 | 168
181 | 163
180 | 160
737 | 144
60 | 138
62 | 138
93 | 138
100 | 138
101 | 138
104 | 138
151 | 138
204 | 138
205 | 138
206 | 138
207 | 138
208 | 138
209 | 138
210 | 138
211 | 138
212 | 138
213 | 138
410 | 138
411 | 138
616 | 138
121 | 137
749 | 136
182 | 135
337 | 135
596 | 135
450 | 134
189 | 131
449 | 119
447 | 114
751 | 90
142 | 81
745 | 67
743 | 66
711 | 56
391 | 42
694 | 24
137 | 19
695 | 13
736 | 11
(636 rows)