Index scan(row count should be less than 8) costs more than table full scan

[winoros]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

create table t(a int, b int, key(a));
insert into t values (52, 9),(40, 74),(68, 14),(79, 96),(21, 46),(19, 8),(82, 14),(92, 3),(322, 331),(359, 275),(956, 642),(238, 792),(266, 266),(976, 485),(711, 823),(748, 514),(115, 709),(147, 695),(203, 517),(391, 786),(316, 337),(758, 255),(686, 926),(667, 863),(126, 973),(986, 172),(566, 271),(308, 409),(534, 659),(314, 619),(643, 328),(404, 847),(695, 294),(168, 553),(450, 599),(438, 396),(610, 454),(492, 794),(376, 151),(904, 858),(875, 835),(531, 677),(209, 461),(765, 720),(183, 861),(121, 884),(675, 954),(883, 287),(535, 812),(594, 925),(838, 692),(140, 953),(592, 728),(879, 489),(192, 879),(142, 430),(483, 562),(940, 613),(679, 451),(938, 792),(566, 656),(305, 255),(356, 342),(250, 963),(260, 846),(118, 279),(329, 986),(382, 997),(203, 227),(107, 881),(261, 308),(117, 504),(728, 669),(406, 341),(807, 635),(566, 966),(943, 614),(242, 361),(863, 135),(969, 447),(307, 490),(926, 855),(297, 821),(484, 307),(456, 591),(205, 964),(822, 424),(852, 537),(881, 427),(840, 576),(414, 664),(783, 654),(907, 579),(561, 745),(541, 668),(509, 362),(756, 430),(675, 930),(775, 865),(339, 628),(592, 692),(489, 960),(552, 176),(785, 711),(863, 179),(497, 611),(383, 866),(893, 291),(498, 382),(424, 292),(378, 162),(195, 309),(803, 863),(718, 255),(188, 169),(937, 418),(940, 487),(449, 241),(998, 983),(795, 212),(102, 942),(760, 503),(468, 160),(524, 196),(695, 238),(377, 444),(200, 486),(758, 887),(703, 602),(756, 628),(417, 269),(712, 453),(493, 233),(955, 191),(558, 432),(594, 433),(738, 220),(622, 110),(598, 961),(361, 311),(236, 535),(349, 544),(302, 608),(475, 694),(195, 908),(586, 215),(402, 661),(413, 672),(881, 375),(666, 583),(790, 754),(359, 836),(230, 166),(172, 786),(282, 804),(904, 263),(165, 421),(462, 219),(248, 933),(525, 675),(284, 731),(901, 893),(689, 276),(736, 641),(381, 243),(185, 225),(945, 604),(225, 190),(508, 342),(110, 703),(118, 447),(734, 616),(246, 543),(397, 634),(868, 492),(544, 704),(282, 276),(139, 224),(565, 716),(265, 806),(554, 866),(765, 962),(777, 759),(763, 816),(635, 283),(313, 665),(596, 995),(747, 566),(617, 428),(758, 387),(695, 541),(553, 804),(504, 334),(772, 623),(560, 566),(245, 356),(196, 870),(677, 703),(443, 616),(907, 515),(520, 685),(501, 142),(359, 125),(991, 357),(211, 544),(666, 953),(927, 948),(271, 384),(709, 153),(300, 147),(982, 473),(324, 246),(393, 660),(608, 744),(672, 686),(446, 304),(607, 683),(967, 681),(318, 452),(297, 674),(829, 193),(726, 445),(134, 569),(808, 983),(276, 119),(952, 596),(200, 147),(118, 826),(823, 462),(220, 748),(871, 490),(278, 777),(128, 560),(729, 555),(383, 386),(721, 965),(689, 266),(992, 543),(838, 791),(270, 737),(409, 836),(720, 241),(229, 285),(271, 530),(650, 782),(517, 866),(455, 258),(337, 585),(329, 399),(941, 512),(845, 439),(159, 898),(122, 563),(464, 624),(270, 886),(337, 460),(515, 950),(341, 583),(286, 593),(903, 597),(800, 341),(247, 940),(424, 275),(211, 557),(718, 893),(970, 962),(358, 569),(163, 589),(249, 856),(387, 141),(274, 494),(350, 818),(722, 501),(748, 711),(587, 682),(933, 813),(314, 232),(366, 400),(850, 218),(855, 996),(257, 135),(558, 222),(113, 481),(331, 619),(895, 207),(135, 367),(989, 364),(612, 432),(758, 243),(764, 108),(380, 828),(345, 449),(711, 258),(304, 684),(193, 230),(385, 101),(514, 547),(771, 656),(363, 598),(794, 727),(158, 902),(910, 103),(809, 228),(739, 204),(127, 436),(737, 197),(374, 855),(553, 717),(688, 814),(640, 445),(264, 354),(375, 782),(200, 164),(267, 355),(312, 608),(700, 812),(415, 729),(275, 799),(963, 311),(978, 612),(488, 867),(416, 264),(342, 655),(473, 671),(207, 144),(299, 206),(218, 151),(748, 412),(804, 561),(304, 688),(595, 974),(665, 575),(651, 140),(304, 602),(546, 854),(225, 664),(897, 420),(587, 678),(747, 346),(376, 999),(797, 942),(268, 139),(747, 687),(123, 110),(814, 918),(660, 866),(128, 613),(780, 785),(562, 728),(591, 204),(711, 484),(885, 157),(205, 468),(532, 737),(239, 102),(693, 927),(544, 879),(801, 549),(681, 175),(169, 230),(466, 418),(339, 615),(877, 807),(194, 374),(945, 593),(552, 549),(603, 120),(346, 769),(377, 540),(152, 513),(695, 650),(153, 101),(374, 405),(442, 871),(666, 745),(761, 626),(909, 579),(903, 790),(284, 684),(707, 461),(356, 421),(603, 781),(456, 889),(278, 513),(816, 220),(363, 675),(519, 130),(664, 129),(289, 361),(151, 332),(126, 216),(415, 297),(511, 998),(161, 287),(383, 573),(301, 406),(979, 759),(260, 476),(692, 308),(277, 587),(106, 269),(345, 441),(474, 968),(246, 791),(106, 495),(388, 805),(627, 690),(636, 752),(142, 367),(750, 497),(786, 921),(694, 138),(751, 116),(912, 629),(933, 503),(705, 932),(524, 630),(145, 796),(146, 243),(854, 742),(391, 603),(754, 676),(101, 616),(300, 266),(721, 953),(649, 398),(206, 325),(499, 123),(123, 613),(361, 484),(432, 776),(852, 627),(996, 398),(597, 686),(336, 909),(891, 310),(251, 536),(634, 400),(371, 185),(215, 210),(692, 645),(746, 887),(231, 181),(404, 942),(731, 616),(235, 183),(143, 816),(357, 323),(161, 223),(523, 734),(275, 330),(842, 663),(206, 163),(958, 150),(539, 953),(679, 332),(226, 300),(732, 420),(785, 273),(507, 711),(619, 958),(208, 821),(584, 672),(216, 940),(383, 672),(837, 914),(690, 682),(456, 400),(466, 279),(802, 371),(150, 327),(724, 242),(860, 649),(993, 427),(960, 298),(928, 979),(818, 236),(632, 850),(422, 897),(609, 363),(749, 763),(127, 639),(113, 235),(759, 161),(662, 480),(423, 139),(628, 146),(853, 773),(851, 704),(371, 889),(980, 121),(753, 506),(882, 854),(380, 733),(763, 500),(610, 835),(710, 262),(417, 976),(512, 505),(792, 756),(489, 795),(192, 960),(898, 409),(817, 221),(804, 505),(672, 193),(756, 774),(898, 381),(432, 232),(560, 334),(925, 797),(469, 882),(975, 863),(499, 283),(884, 242),(708, 912),(396, 539),(995, 923),(445, 703),(294, 383),(297, 953),(480, 670),(367, 846),(907, 474),(111, 369),(530, 301),(806, 726),(229, 452),(206, 194),(844, 191),(285, 633),(486, 505),(303, 104),(835, 898),(878, 421),(247, 103),(336, 523),(994, 658),(954, 317),(855, 350),(665, 730),(787, 727),(137, 958),(827, 867),(769, 332),(197, 969),(456, 172),(838, 390),(202, 584),(730, 803),(652, 147),(676, 559),(392, 805),(668, 333),(509, 423),(763, 716),(217, 938),(683, 897),(828, 237),(892, 416),(513, 541),(531, 145),(476, 495),(612, 300),(594, 761),(698, 162),(662, 883),(945, 311),(158, 460),(820, 982),(500, 692),(171, 589),(951, 143),(191, 109),(435, 568),(949, 295),(655, 245),(458, 367),(510, 423),(623, 180),(970, 431),(547, 577),(849, 215),(406, 674),(142, 829),(471, 640),(212, 784),(540, 484),(178, 228),(943, 159),(803, 405),(725, 112),(283, 265),(693, 186),(230, 389),(650, 168),(664, 877),(391, 139),(477, 869),(489, 233),(785, 484),(905, 534),(129, 810),(603, 459),(722, 879),(444, 965),(999, 515),(617, 218),(407, 976),(527, 269),(393, 984),(448, 914),(797, 174),(221, 522),(603, 863),(833, 191),(939, 770),(485, 173),(491, 361),(622, 519),(929, 370),(132, 295),(584, 177),(832, 629),(893, 325),(188, 724),(295, 197),(461, 320),(588, 751),(772, 178),(274, 168),(381, 900),(999, 904),(190, 814),(311, 706),(691, 636),(163, 522),(618, 703),(409, 943),(802, 408),(793, 318),(793, 508),(981, 279),(379, 359),(110, 774),(918, 949),(124, 885),(153, 208),(119, 608),(866, 689),(235, 774),(333, 512),(128, 577),(156, 687),(286, 387),(667, 286),(417, 328),(158, 903),(358, 476),(233, 207),(659, 819),(970, 106),(705, 897),(468, 484),(833, 241),(933, 688),(918, 202),(251, 726),(207, 384),(885, 546),(471, 631),(975, 311),(665, 152),(764, 605),(120, 735),(960, 399),(481, 345),(271, 303),(821, 522),(900, 633),(549, 360),(302, 854),(349, 584),(905, 388),(973, 479),(185, 977),(838, 341),(239, 175),(740, 163),(896, 714),(298, 440),(232, 817),(410, 178),(358, 445),(265, 283),(225, 245),(384, 538),(987, 904),(551, 506),(700, 914),(358, 397),(432, 864),(280, 991),(960, 541),(796, 598),(325, 548),(685, 567),(220, 622),(494, 379),(419, 988),(149, 411),(702, 560),(644, 444),(851, 922),(674, 773),(260, 125),(199, 620),(795, 933),(634, 825),(304, 611),(627, 697),(654, 738),(979, 785),(874, 319),(620, 567),(674, 937),(649, 839),(964, 454),(505, 133),(208, 433),(401, 154),(618, 143),(469, 299),(108, 220),(320, 219),(455, 876),(485, 634),(237, 322),(505, 834),(630, 747),(186, 606),(134, 188),(999, 890),(196, 887),(284, 761),(958, 323),(988, 838),(812, 910),(530, 528),(426, 471),(676, 369),(821, 213),(911, 575),(961, 409),(585, 737),(440, 900),(842, 283),(287, 323),(627, 422),(274, 188),(611, 752),(502, 199),(788, 728),(614, 672),(984, 722),(123, 609),(353, 649),(858, 924),(569, 949),(923, 225),(422, 961),(168, 687),(148, 972),(604, 483),(111, 833),(108, 189),(758, 887),(486, 237),(224, 869),(874, 790),(554, 995),(892, 832),(419, 602),(358, 856),(208, 266),(964, 302),(157, 607),(761, 689),(736, 367),(377, 178),(392, 695),(645, 237),(153, 595),(243, 873),(596, 410),(448, 406),(144, 683),(632, 982),(771, 299),(673, 224),(105, 921),(177, 796),(787, 448),(518, 610),(497, 338),(329, 133),(341, 619),(767, 514),(408, 730),(421, 964),(915, 239),(862, 811),(863, 509),(483, 381),(502, 878),(831, 389),(768, 523),(415, 590),(768, 324),(194, 698),(823, 181),(393, 751),(636, 626),(167, 442),(109, 417),(613, 922),(128, 657),(378, 982),(442, 393),(268, 773),(659, 533),(920, 628),(269, 419),(232, 260),(301, 331),(183, 611),(638, 510),(942, 847),(408, 623),(729, 351),(482, 711),(928, 846),(885, 422),(335, 745),(676, 243),(565, 330),(883, 110),(267, 986),(790, 658),(477, 953),(111, 381),(303, 577),(846, 636),(217, 589),(120, 137),(589, 147),(860, 970),(275, 337),(930, 926),(845, 298),(500, 132),(966, 648),(256, 205),(239, 160),(665, 844),(898, 911),(480, 727),(397, 118),(984, 164),(884, 895),(431, 191),(577, 664),(737, 605),(380, 332),(396, 710),(964, 195),(212, 541),(113, 285),(287, 315),(278, 226),(518, 919),(489, 165),(974, 617),(519, 224),(740, 800),(268, 733),(721, 500),(954, 206),(699, 356),(549, 717),(102, 714),(708, 343),(959, 126),(266, 651),(746, 667),(775, 204),(223, 475),(595, 379),(304, 666),(593, 575),(631, 331),(323, 763),(344, 175),(978, 140),(434, 980),(131, 738),(908, 341),(267, 517),(787, 692),(108, 816),(151, 864),(722, 264),(365, 777),(368, 157),(241, 719),(520, 768),(670, 388),(163, 151),(553, 918),(709, 187),(108, 482),(758, 498),(360, 248),(927, 400),(423, 697),(399, 705),(623, 380),(552, 147),(156, 410),(598, 261),(821, 879),(619, 803),(302, 180),(524, 351),(840, 564),(287, 197),(800, 233),(134, 951),(961, 710),(158, 179),(989, 427),(991, 432),(388, 638),(231, 739),(441, 254),(719, 863),(332, 465),(134, 841),(388, 358),(745, 101),(957, 192),(171, 901),(390, 672),(815, 139),(245, 833),(167, 149),(649, 905),(395, 211),(156, 222),(981, 933),(779, 230),(257, 435),(261, 458),(414, 922),(749, 777),(916, 901),(665, 581),(626, 593),(331, 642),(978, 799),(421, 445),(758, 852),(496, 507),(240, 161),(164, 792),(829, 178),(810, 893),(457, 979),(833, 558),(249, 777),(733, 942),(397, 219),(366, 613),(763, 745),(104, 680),(780, 532),(806, 843),(843, 213),(739, 484),(787, 659),(464, 407),(629, 630),(175, 618),(127, 946),(497, 565),(880, 328),(570, 277),(126, 592),(136, 441),(929, 319);

analyze table t;

To make the test stable, you can create a config file

[performance]
stats-lease = "0"

or just wait a little to make sure that the modify_count is 0 when testing.

2. What did you expect to see? (Required)

for SQL select * from tnormal where a<100;, choose index scan by default. (the estimation result should smaller than 8).

3. What did you see instead (Required)

mysql> explain format='verbose' select * from tnormal use index(a) where a<100;
+-------------------------------+---------+----------+-----------+---------------------------+------------------------------------+
| id                            | estRows | estCost  | task      | access object             | operator info                      |
+-------------------------------+---------+----------+-----------+---------------------------+------------------------------------+
| IndexLookUp_7                 | 9.87    | 19260.45 | root      |                           |                                    |
| ├─IndexRangeScan_5(Build)     | 9.87    | 2008.22  | cop[tikv] | table:tnormal, index:a(a) | range:[-inf,100), keep order:false |
| └─TableRowIDScan_6(Probe)     | 9.87    | 2243.17  | cop[tikv] | table:tnormal             | keep order:false                   |
+-------------------------------+---------+----------+-----------+---------------------------+------------------------------------+
3 rows in set (0.00 sec)

mysql> explain format='verbose' select * from tnormal where a<100;
+-------------------------+---------+-----------+-----------+---------------+-------------------------+
| id                      | estRows | estCost   | task      | access object | operator info           |
+-------------------------+---------+-----------+-----------+---------------+-------------------------+
| TableReader_7           | 7.89    | 18547.23  | root      |               | data:Selection_6        |
| └─Selection_6           | 7.89    | 277207.97 | cop[tikv] |               | lt(test.tnormal.a, 100) |
|   └─TableFullScan_5     | 1000.00 | 227307.97 | cop[tikv] | table:tnormal | keep order:false        |
+-------------------------+---------+-----------+-----------+---------------+-------------------------+
3 rows in set (0.00 sec)

It chooses table scan by default. The row count estimation of the index is larger than the table scan. (9.87 vs 7.89).

4. What is your TiDB version? (Required)

current master.