Many hyperlinks are disabled.
Use anonymous login
to enable hyperlinks.
Overview
Comment: | Add tests to check if ANALYZE is choosing common non-periodic samples for the stat4 table. |
---|---|
Downloads: | Tarball | ZIP archive |
Timelines: | family | ancestors | descendants | both | trunk |
Files: | files | file ages | folders |
SHA1: |
175842997af134138784bff6f8e93573 |
User & Date: | dan 2013-09-02 11:52:11.512 |
Context
2013-09-02
| ||
18:58 | Further stat4 related tests. (check-in: 0a702c4b4c user: dan tags: trunk) | |
11:52 | Add tests to check if ANALYZE is choosing common non-periodic samples for the stat4 table. (check-in: 175842997a user: dan tags: trunk) | |
07:16 | Fix a problem with using stat4 data to estimate the number of rows scanned by a range constraint on the second or subsequent column of any index where an affinity transformation must be applied to the constraint argument. (check-in: c21f58d848 user: dan tags: trunk) | |
Changes
Changes to test/analyze9.test.
︙ | ︙ | |||
607 608 609 610 611 612 613 | do_eqp_test 13.2.1 { SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1 } {/SEARCH TABLE t1 USING INDEX i1/} do_eqp_test 13.2.2 { SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1 } {/SEARCH TABLE t1 USING INDEX i1/} | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | > > > | > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > | 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 | do_eqp_test 13.2.1 { SELECT * FROM t1 WHERE a='ott' AND b<10 AND c=1 } {/SEARCH TABLE t1 USING INDEX i1/} do_eqp_test 13.2.2 { SELECT * FROM t1 WHERE a='ott' AND b<'10' AND c=1 } {/SEARCH TABLE t1 USING INDEX i1/} #------------------------------------------------------------------------- # By default, 16 non-periodic samples are collected for the stat4 table. # The following tests attempt to verify that the most common keys are # being collected. # proc check_stat4 {tn} { db eval ANALYZE db eval {SELECT a, b, c, d FROM t1} { incr k($a) incr k([list $a $b]) incr k([list $a $b $c]) if { [info exists k([list $a $b $c $d])]==0 } { incr nRow } incr k([list $a $b $c $d]) } set L [list] foreach key [array names k] { lappend L [list $k($key) $key] } set nSample $nRow if {$nSample>16} {set nSample 16} set nThreshold [lindex [lsort -decr -integer -index 0 $L] [expr $nSample-1] 0] foreach key [array names k] { if {$k($key)>$nThreshold} { set expect($key) 1 } if {$k($key)==$nThreshold} { set possible($key) 1 } } set nPossible [expr $nSample - [llength [array names expect]]] #puts "EXPECT: [array names expect]" #puts "POSSIBLE($nPossible/[array size possible]): [array names possible]" #puts "HAVE: [db eval {SELECT test_decode(sample) FROM sqlite_stat4 WHERE idx='i1'}]" db eval {SELECT test_decode(sample) AS s FROM sqlite_stat4 WHERE idx='i1'} { set seen 0 for {set i 0} {$i<4} {incr i} { unset -nocomplain expect([lrange $s 0 $i]) if {[info exists possible([lrange $s 0 $i])]} { set seen 1 unset -nocomplain possible([lrange $s 0 $i]) } } if {$seen} {incr nPossible -1} } if {$nPossible<0} {set nPossible 0} set res [list [llength [array names expect]] $nPossible] uplevel [list do_test $tn [list set {} $res] {0 0}] } drop_all_tables do_test 14.1.1 { execsql { CREATE TABLE t1(a,b,c,d); CREATE INDEX i1 ON t1(a,b,c,d); } for {set i 0} {$i < 160} {incr i} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } if {($i % 10)==0} { execsql { INSERT INTO t1 VALUES($i,$i,$i,$i) } } } } {} check_stat4 14.1.2 do_test 14.2.1 { execsql { DELETE FROM t1 } for {set i 0} {$i < 1600} {incr i} { execsql { INSERT INTO t1 VALUES($i/10,$i/17,$i/27,$i/37) } } } {} check_stat4 14.2.2 do_test 14.3.1 { for {set i 0} {$i < 10} {incr i} { execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } execsql { INSERT INTO t1 VALUES($i*50,$i*50,$i*50,$i*50) } } } {} check_stat4 14.3.2 finish_test |