On Sun, Dec 21, 2008 at 10:25:59PM -0500, Robert Haas wrote: > [Some performance testing.]
I (finally!) have a chance to post my performance testing results... my
apologies for the really long delay. <Excuses omitted>
Unfortunately I'm not seeing wonderful speedups with the particular
queries I did in this case. I generated three 1GB datasets, with skews
set at 1, 2, and 3. The test script I wrote turns on enable_usestatmcvs
and runs EXPLAIN ANALYZE on the same query five times. Then it turns
enable_usestatmcvs off, and runs the same query five more times. It does
this with each of the three datasets in turn, and then starts over at
the beginning until I tell it to quit. My results showed a statistically
significant improvement in speed only on the skew == 3 dataset.
I did the same tests twice, once with default_statistics_target set to
10, and once with it set to 100. I've attached boxplots of the total
query times as reported by EXPLAIN ANALYZE ("dst10" in the filename
indicates default_statistics_target was 10, and so on), my results
parsed out of the EXPLAIN ANALYZE output (test.filtered.10 and
test.filtered.100), the results of one-tailed Student's T tests of the
result set (ttests), and the R code to run the tests if anyone's really
interested (t.test.R).
The results data includes six columns: the skew value, whether
enable_usestatmcvs was on or not (represented by a 1 or 0), total times
for each of the three joins that made up the query, and total time for
the query itself. The results above pay attention only to the total
query time.
Finally, the query involved:
SELECT * FROM lineitem l LEFT JOIN part p ON (p.p_partkey = l.l_partkey)
LEFT JOIN orders o ON (o.o_orderkey = l.l_orderky) LEFT JOIN customer c
ON (c.c_custkey = o.o_custkey);
- Josh / eggyknap
<<attachment: boxplot-dst10.png>>
<<attachment: boxplot-dst100.png>>
SKEW USESTAT J1 J2 J3 TOT 1 1 50461.443000 397244.673000 453217.081000 459501.492 1 1 47884.085000 392737.144000 453039.924000 460809.210 1 1 52175.049000 473484.660000 518528.660000 523864.739 1 1 47127.359000 463970.123000 510257.929000 515556.171 1 1 49382.039000 492098.877000 542123.146000 547503.329 1 0 43094.980000 464022.565000 509026.652000 514349.238 1 0 45901.734000 439642.013000 490180.994000 495489.335 1 0 43127.400000 430072.203000 475914.797000 481192.279 1 0 42070.676000 375572.825000 423910.457000 429677.988 1 0 56491.288000 498455.906000 551204.091000 557467.631 2 1 58372.411000 461959.358000 508724.227000 514004.653 2 1 55187.182000 451564.246000 497331.791000 502957.730 2 1 61093.577000 443683.358000 493160.552000 498868.413 2 1 55299.883000 482283.701000 541617.568000 548030.650 2 1 54002.928000 499089.964000 544504.041000 549828.715 2 0 56133.232000 452656.945000 501956.569000 507287.362 2 0 56900.880000 478264.522000 537943.058000 544455.088 2 0 61512.999000 480176.724000 541688.121000 548684.876 2 0 55106.671000 474847.360000 522074.604000 527428.018 2 0 57440.536000 512357.019000 558515.194000 563922.575 3 1 48912.233000 519270.741000 562948.024000 568318.976 3 1 51509.014000 455114.005000 502253.369000 507639.017 3 1 48977.903000 399254.515000 442796.459000 448157.712 3 1 52664.751000 398226.595000 444402.503000 449745.454 3 1 57036.981000 498623.476000 541792.070000 547105.638 3 0 53972.755000 490592.656000 544792.700000 550086.185 3 0 59046.762000 490597.511000 534615.830000 539919.402 3 0 49112.387000 517318.422000 574361.142000 581877.479 3 0 50138.407000 499705.817000 545116.168000 550505.373 3 0 48691.832000 510223.136000 564247.448000 570378.601 1 1 68256.834000 496599.310000 557998.082000 565697.676 1 1 56864.637000 456848.446000 502898.716000 508340.867 1 1 53933.953000 479646.739000 528711.936000 534046.589 1 1 56468.009000 456499.306000 503936.705000 509286.867 1 1 56117.481000 464881.592000 511655.733000 517015.575 1 0 60140.954000 466226.599000 519332.729000 524760.071 1 0 56106.889000 487886.698000 544010.570000 550316.703 1 0 62452.804000 509665.970000 556011.068000 561309.527 1 0 58373.154000 468318.808000 515009.584000 520342.427 1 0 52479.479000 499852.717000 546099.564000 551457.608 2 1 58950.898000 487229.024000 535760.246000 541083.469 2 1 77649.141000 542007.659000 596702.949000 602057.034 2 1 66791.422000 491053.890000 537054.119000 542319.402 2 1 62225.071000 490657.271000 541682.296000 547082.707 2 1 58858.777000 480423.571000 537623.121000 543651.606 2 0 61058.416000 459218.376000 507400.814000 512990.429 2 0 57799.463000 473552.646000 527838.242000 533844.748 2 0 57729.922000 457128.882000 504043.485000 509316.546 2 0 56149.353000 499584.712000 546124.430000 551542.697 2 0 51019.423000 496109.051000 543427.022000 549035.240 3 1 57750.728000 516360.030000 573497.823000 578914.504 3 1 82154.351000 519556.207000 570020.298000 575333.085 3 1 56701.722000 530956.460000 580205.311000 585577.573 3 1 61998.805000 454719.211000 502666.593000 507965.137 3 1 64165.963000 539947.641000 589797.442000 595414.711 3 0 63376.744000 511260.626000 576367.468000 583848.296 3 0 53568.663000 518933.081000 583806.790000 590952.092 3 0 56417.634000 523775.047000 585754.163000 592916.161 3 0 67336.407000 494275.291000 547850.263000 553236.563 3 0 62619.519000 546665.608000 597983.649000 603349.294 1 1 45632.934000 460209.018000 506394.639000 511702.040 1 1 57796.239000 517464.271000 562457.456000 567777.361 1 1 59146.608000 492399.274000 540666.064000 546082.620 1 1 65756.510000 485957.866000 536457.742000 541774.797 1 1 59003.579000 514410.538000 563941.148000 569314.587 1 0 57958.739000 478292.901000 548359.945000 555347.906 1 0 65731.034000 511164.679000 556270.733000 561613.671 1 0 53741.178000 488060.310000 537985.944000 543345.939 1 0 55453.261000 502884.470000 549426.551000 554766.455 1 0 56059.441000 491798.414000 542778.675000 548813.913 2 1 48596.584000 502989.914000 562050.723000 569787.369 2 1 57625.455000 499235.651000 550236.044000 555894.098 2 1 68040.832000 521142.773000 572382.288000 577696.900 2 1 58615.857000 528877.404000 577349.182000 582695.988 2 1 59611.234000 537756.619000 587570.847000 592875.282 2 0 45110.394000 483331.895000 530003.937000 535400.670 2 0 58559.223000 483617.105000 530661.821000 535997.009 2 0 60052.985000 563698.826000 622438.867000 629831.602 2 0 56899.898000 480354.456000 525623.280000 530974.827 2 0 53473.312000 471607.384000 520982.675000 526355.763 3 1 64731.160000 482400.498000 530851.728000 536314.695 3 1 59734.274000 483720.843000 530119.165000 535624.370 3 1 62426.846000 507789.349000 556988.976000 562387.794 3 1 64621.196000 541142.911000 594214.349000 601412.025 3 1 77450.786000 559039.823000 626024.466000 632531.572 3 0 66718.132000 541829.513000 625977.710000 633140.590 3 0 54720.360000 575373.455000 664481.808000 671967.388 3 0 55711.531000 584629.602000 633781.216000 639088.660 3 0 60053.469000 480705.037000 540869.617000 548643.377 3 0 56616.806000 526914.124000 587528.239000 594879.844
SKEW USESTAT J1 J2 J3 TOT 1 1 70975.977000 490748.603000 537285.899000 542822.397 1 1 51285.251000 460450.036000 516394.147000 523753.699 1 1 46893.282000 475807.102000 522704.638000 528069.598 1 1 45599.801000 447489.410000 491672.114000 497086.948 1 1 51978.483000 492317.341000 566450.733000 574931.519 1 0 45143.452000 462508.578000 514129.816000 519496.445 1 0 45792.746000 416293.291000 458770.184000 464115.774 1 0 44549.418000 449191.620000 492629.976000 497978.740 1 0 51043.041000 439145.906000 488797.041000 494715.040 1 0 42933.202000 436764.218000 484668.669000 490318.247 2 1 46435.636000 424206.863000 469714.998000 475017.171 2 1 44634.453000 452407.981000 502224.853000 507528.524 2 1 44301.559000 434112.925000 481094.136000 486419.660 2 1 41573.961000 453646.715000 517399.624000 522924.335 2 1 42549.006000 454462.970000 505379.051000 510688.309 2 0 43178.170000 457193.616000 500506.088000 506019.595 2 0 44628.376000 476965.739000 543955.761000 550880.618 2 0 47184.140000 427061.891000 473832.599000 479199.594 2 0 46795.202000 451782.752000 495524.435000 500844.244 2 0 45507.311000 434592.180000 483533.148000 489053.687 3 1 46849.490000 445017.503000 493137.809000 498514.082 3 1 50493.414000 475010.258000 518368.969000 523672.759 3 1 48510.668000 469228.635000 522028.081000 527804.347 3 1 69279.826000 463357.687000 507600.221000 512903.690 3 1 57084.317000 468614.974000 532509.023000 539100.901 3 0 47190.961000 439715.321000 487259.708000 492868.783 3 0 51166.651000 475079.331000 533326.034000 540994.357 3 0 51597.512000 464361.843000 508566.154000 513872.855 3 0 47018.874000 432064.414000 513842.149000 519840.711 3 0 48503.817000 473057.612000 524352.311000 530565.222 1 1 48325.743000 422145.135000 478404.111000 483799.536 1 1 49662.652000 438888.160000 488005.528000 493281.197 1 1 54543.442000 452155.219000 498832.404000 504374.861 1 1 53946.279000 443121.542000 490380.780000 495737.206 1 1 49850.711000 425130.385000 482547.763000 487896.975 1 0 45482.972000 457790.270000 509417.234000 516043.993 1 0 60149.632000 486217.387000 531769.657000 537085.881 1 0 43249.877000 451594.056000 499280.337000 504562.665 1 0 47259.358000 422165.865000 478713.034000 485788.464 1 0 51778.501000 498760.528000 548994.372000 554336.262 2 1 49646.524000 455020.561000 507148.648000 512535.115 2 1 47275.599000 425150.152000 490439.660000 497773.938 2 1 49849.183000 459903.059000 520741.192000 528466.211 2 1 45755.035000 436281.536000 485759.082000 491925.474 2 1 47970.284000 441445.522000 489473.272000 494758.668 2 0 44601.614000 457367.473000 510410.861000 515856.845 2 0 46658.537000 463053.674000 511511.661000 517191.430 2 0 46338.209000 445518.927000 494267.680000 499716.835 2 0 54320.039000 455528.735000 499444.708000 504737.802 2 0 47227.392000 486397.155000 536412.699000 542538.095 3 1 48700.665000 389892.272000 456769.940000 463974.268 3 1 53641.337000 441842.579000 494070.985000 500322.647 3 1 58316.661000 437080.856000 483062.448000 488552.725 3 1 49276.868000 448446.622000 494107.708000 499440.321 3 1 45084.470000 430422.226000 477264.117000 483175.037 3 0 47223.091000 505122.430000 558655.277000 564453.517 3 0 52308.548000 495376.908000 539090.032000 544573.545 3 0 51866.631000 396245.822000 443946.152000 449260.448 3 0 49904.066000 514046.370000 576229.741000 581728.123 3 0 52907.983000 535330.867000 594595.471000 600060.755
[1] "====================================================================="
[1] "One-tailed Student's T test, SKEW = 1, default_statistics_target = 10"
Welch Two Sample t-test
data: s10$TOT[s10$SKEW == 1 & s10$USESTAT == 0] and s10$TOT[s10$SKEW == 1 &
s10$USESTAT == 1]
t = 0.3694, df = 27.749, p-value = 0.3573
alternative hypothesis: true difference in means is greater than 0
95 percent confidence interval:
-17303.79 Inf
sample estimates:
mean of x mean of y
530016.7 525218.3
[1] "====================================================================="
[1] "One-tailed Student's T test, SKEW = 2, default_statistics_target = 10"
Welch Two Sample t-test
data: s10$TOT[s10$SKEW == 2 & s10$USESTAT == 0] and s10$TOT[s10$SKEW == 2 &
s10$USESTAT == 1]
t = -0.9827, df = 27.964, p-value = 0.8329
alternative hypothesis: true difference in means is greater than 0
95 percent confidence interval:
-29454.19 Inf
sample estimates:
mean of x mean of y
540471.2 551255.6
[1] "====================================================================="
[1] "One-tailed Student's T test, SKEW = 3, default_statistics_target = 10"
Welch Two Sample t-test
data: s10$TOT[s10$SKEW == 3 & s10$USESTAT == 0] and s10$TOT[s10$SKEW == 3 &
s10$USESTAT == 1]
t = 2.2689, df = 25.44, p-value = 0.01600
alternative hypothesis: true difference in means is greater than 0
95 percent confidence interval:
9449.36 Inf
sample estimates:
mean of x mean of y
586986.0 548829.5
[1] "====================================================================="
[1] "One-tailed Student's T test, SKEW = 1, default_statistics_target = 100"
Welch Two Sample t-test
data: s100$TOT[s100$SKEW == 1 & s100$USESTAT == 0] and s100$TOT[s100$SKEW == 1
& s100$USESTAT == 1]
t = -0.5446, df = 17.821, p-value = 0.7036
alternative hypothesis: true difference in means is greater than 0
95 percent confidence interval:
-28177.18 Inf
sample estimates:
mean of x mean of y
506444.2 513175.4
[1] "====================================================================="
[1] "One-tailed Student's T test, SKEW = 2, default_statistics_target = 100"
Welch Two Sample t-test
data: s100$TOT[s100$SKEW == 2 & s100$USESTAT == 0] and s100$TOT[s100$SKEW == 2
& s100$USESTAT == 1]
t = 0.8893, df = 16.688, p-value = 0.1932
alternative hypothesis: true difference in means is greater than 0
95 percent confidence interval:
-7475.02 Inf
sample estimates:
mean of x mean of y
510603.9 502803.7
[1] "====================================================================="
[1] "One-tailed Student's T test, SKEW = 3, default_statistics_target = 100"
Welch Two Sample t-test
data: s100$TOT[s100$SKEW == 3 & s100$USESTAT == 0] and s100$TOT[s100$SKEW == 3
& s100$USESTAT == 1]
t = 1.9314, df = 13.491, p-value = 0.03736
alternative hypothesis: true difference in means is greater than 0
95 percent confidence interval:
2575.890 Inf
sample estimates:
mean of x mean of y
533821.8 503746.1
s10 <- data.frame(read.table('test.filtered.10', header=T))
s100 <- data.frame(read.table('test.filtered.100', header=T))
print("=====================================================================")
print("One-tailed Student's T test, SKEW = 1, default_statistics_target = 10")
t.test(s10$TOT[s10$SKEW == 1 & s10$USESTAT == 0], s10$TOT[s10$SKEW == 1 &
s10$USESTAT == 1], alternative='g')
print("=====================================================================")
print("One-tailed Student's T test, SKEW = 2, default_statistics_target = 10")
t.test(s10$TOT[s10$SKEW == 2 & s10$USESTAT == 0], s10$TOT[s10$SKEW == 2 &
s10$USESTAT == 1], alternative='g')
print("=====================================================================")
print("One-tailed Student's T test, SKEW = 3, default_statistics_target = 10")
t.test(s10$TOT[s10$SKEW == 3 & s10$USESTAT == 0], s10$TOT[s10$SKEW == 3 &
s10$USESTAT == 1], alternative='g')
print("=====================================================================")
print("One-tailed Student's T test, SKEW = 1, default_statistics_target = 100")
t.test(s100$TOT[s100$SKEW == 1 & s100$USESTAT == 0], s100$TOT[s100$SKEW == 1 &
s100$USESTAT == 1], alternative='g')
print("=====================================================================")
print("One-tailed Student's T test, SKEW = 2, default_statistics_target = 100")
t.test(s100$TOT[s100$SKEW == 2 & s100$USESTAT == 0], s100$TOT[s100$SKEW == 2 &
s100$USESTAT == 1], alternative='g')
print("=====================================================================")
print("One-tailed Student's T test, SKEW = 3, default_statistics_target = 100")
t.test(s100$TOT[s100$SKEW == 3 & s100$USESTAT == 0], s100$TOT[s100$SKEW == 3 &
s100$USESTAT == 1], alternative='g')
signature.asc
Description: Digital signature
