Closed 634750802 closed 1 year ago
Execution Plan:
+----------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_14 | 200.00 | root | | gharchive_dev.github_repos.repo_name, Column#61 |
| └─Limit_17 | 200.00 | root | | offset:0, count:200 |
| └─Selection_19 | 200.00 | root | | gt(Column#61, 5000) |
| └─StreamAgg_28 | 200.00 | root | | group by:gharchive_dev.github_repos.repo_name, funcs:count(1)->Column#61, funcs:firstrow(gharchive_dev.github_repos.repo_name)->gharchive_dev.github_repos.repo_name |
| └─IndexJoin_112 | 200.00 | root | | inner join, inner:IndexReader_111, outer key:gharchive_dev.github_repos.repo_id, inner key:gharchive_dev.github_events.repo_id, equal cond:eq(gharchive_dev.github_repos.repo_id, gharchive_dev.github_events.repo_id) |
| ├─IndexReader_116(Build) | 2304.20 | root | | index:IndexFullScan_115 |
| │ └─IndexFullScan_115 | 2304.20 | cop[tikv] | table:gr, index:index_gr_on_repo_name(repo_name) | keep order:true |
| └─IndexReader_111(Probe) | 200.00 | root | partition:pull_request_event | index:IndexRangeScan_110 |
| └─IndexRangeScan_110 | 200.00 | cop[tikv] | table:ge, index:index_github_events_on_repo_id_type_action_month_actor_login(repo_id, type, action, event_month, actor_login) | range: decided by [eq(gharchive_dev.github_events.repo_id, gharchive_dev.github_repos.repo_id) eq(gharchive_dev.github_events.type, PullRequestEvent) eq(gharchive_dev.github_events.action, opened)], keep order:false |
+----------------------------------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
May be it should use tiflash to execute it, which is only cost 15.435s:
SELECT /*+ read_from_storage(tiflash[ge, gr]) */
`gr`.`repo_name`,
COUNT(*) AS `pr_count`
FROM
`github_events` AS `ge`
INNER JOIN `github_repos` AS `gr` ON `ge`.`repo_id` = `gr`.`repo_id`
WHERE
`ge`.`type` = 'PullRequestEvent'
AND `ge`.`action` = 'opened'
GROUP BY
`gr`.`repo_name`
HAVING
`pr_count` > 5000
LIMIT
200
If you optimize this SQL to reduce the unnecessary join in the following way, it only costs 2.549s.
WITH top200repos AS (
SELECT
`ge`.`repo_id`,
COUNT(*) AS `pr_count`
FROM
`github_events` AS `ge`
WHERE
`ge`.`type` = 'PullRequestEvent'
AND `ge`.`action` = 'opened'
GROUP BY
`ge`.`repo_id`
HAVING
`pr_count` > 5000
LIMIT 200
)
SELECT gr.repo_id, gr.repo_name, tr.pr_count
FROM github_repos gr
JOIN top200repos tr ON gr.repo_id = tr.repo_id
ORDER BY tr.pr_count DESC
+-----------+---------------------------------------------------------+----------+
| repo_id | repo_name | pr_count |
+-----------+---------------------------------------------------------+----------+
| 408476462 | mhutchinson/mhutchinson-distributor | 282409 |
| 7833168 | elastic/kibana | 90931 |
| 19745004 | odoo/odoo | 77538 |
| 53436532 | sauron-demo/sauron | 54807 |
| 3638964 | ansible/ansible | 45823 |
| 356421872 | test-organization-kkjeer/bot-validation-2 | 45442 |
| 44838949 | apple/swift | 44109 |
| 316625790 | itsprivate/ts | 43123 |
| 157935408 | rdxvsrmv/OfficeDocs-OfficeUpdates-test | 36689 |
| 873328 | getsentry/sentry | 34134 |
| 75224144 | openshift/release | 32594 |
| 126398186 | argo-testing/app | 31847 |
| 163379278 | OPS-E2E-PPE/E2E_DocFxV3 | 30862 |
| 15111821 | grafana/grafana | 28957 |
| 28457823 | freeCodeCamp/freeCodeCamp | 28720 |
| 27193779 | nodejs/node | 28136 |
| 51478266 | kubernetes/website | 27166 |
| 11789497 | ideatest1/PullRequestTest | 26295 |
| 8495499 | civicrm/civicrm-core | 24476 |
| 2928948 | Azure/azure-sdk-for-java | 22897 |
| 7691631 | moby/moby | 22464 |
| 376950716 | boost-e2e-stage-ci-buildkite/non-main-pr | 22439 |
| 56942570 | Homebrew/linuxbrew-core | 22341 |
| 24195339 | angular/angular | 21978 |
| 81908100 | OPS-E2E-PPE/E2E_DocsBranch_Dynamic | 21110 |
| 4508653 | ocaml/opam-repository | 20835 |
| 20587599 | apache/flink | 20732 |
| 201546443 | trustwallet/assets | 20720 |
| 2928944 | Azure/azure-sdk-for-net | 20637 |
| 35890081 | dotnet/docs | 19979 |
| 4127088 | Azure/azure-sdk-for-python | 19491 |
| 33202667 | department-of-veterans-affairs/vets-website | 19199 |
| 60243197 | nextcloud/server | 18833 |
| 380926508 | killah-t-cell/NeuralPDE.jl | 18174 |
| 27561102 | stamparm/maltrail | 18170 |
| 22442668 | openshift/origin | 18151 |
| 30092893 | dotnet/coreclr | 17672 |
| 23083156 | mui/material-ui | 17348 |
| 6763587 | PrestaShop/PrestaShop | 16907 |
| 97922418 | leanprover-community/mathlib | 16560 |
| 108311273 | Azure/azure-sdk-for-js | 16536 |
| 5367323 | akeneo/pim-community-dev | 16405 |
| 281500451 | Skyrat-SS13/Skyrat-tg | 16382 |
| 492880233 | scalr-automation/terraform-scalr-flat-mirror2 | 16219 |
| 27737393 | qmk/qmk_firmware | 16008 |
| 249223036 | nomunomu0504/covid19 | 15985 |
| 791611 | mono/mono | 15721 |
| 32873313 | systemd/systemd | 15555 |
| 110198384 | brave/brave-core | 15423 |
| 196353673 | taosdata/TDengine | 14918 |
| 2967233 | DataDog/documentation | 14902 |
| 142542006 | yunionio/cloudpods | 14785 |
| 418839999 | wsbforg4/atomist-docker-tutorial | 14751 |
| 492881038 | scalr-automation/terraform-scalr-flat-mirror5 | 14717 |
| 405018546 | truecharts/containers | 14397 |
| 120966336 | yogstation13/Yogstation | 14174 |
| 68312233 | microsoft/vcpkg | 13761 |
| 196414933 | open-telemetry/opentelemetry-collector-contrib | 13352 |
| 194751498 | AzureSDKAutomation/azure-sdk-for-java | 13302 |
| 70107786 | vercel/next.js | 13071 |
| 10270250 | facebook/react | 12601 |
| 16416867 | mozilla/addons-server | 12441 |
| 31288958 | hashicorp/vault | 12351 |
| 73648678 | forem/forem | 12278 |
| 2060910 | voidlinux/void-packages | 12257 |
| 16408992 | neovim/neovim | 12024 |
| 39464018 | apache/superset | 11791 |
| 50167042 | grokcode/grokcode.github.io | 11745 |
| 376948559 | boost-e2e-stage-ci-buildkite/pr-comments | 11743 |
| 189621607 | github/docs | 11507 |
| 6899875 | opf/openproject | 11452 |
| 129436456 | openshift/console | 11366 |
| 14447732 | msys2/MINGW-packages | 11320 |
| 84665083 | HippieStation/HippieStationdeprecated2020 | 11318 |
| 91328793 | selenium-sso-user/github-project | 10579 |
| 139590616 | kyma-project/kyma | 10421 |
| 37426721 | pisilinux/main | 10403 |
| 22887404 | artsy/force | 10368 |
| 481452780 | mo9a7i/time_now | 10264 |
| 18524934 | Roll20/roll20-character-sheets | 10225 |
| 283046497 | airbytehq/airbyte | 10204 |
| 11125589 | keycloak/keycloak | 10127 |
| 656494 | cakephp/cakephp | 9844 |
| 22456440 | ppy/osu | 9833 |
| 45069467 | hail-is/hail | 9817 |
| 377787916 | ballerina-platform/ballerina-performance-cloud | 9690 |
| 155220641 | huggingface/transformers | 9652 |
| 1050944 | infinispan/infinispan | 9600 |
| 216948313 | AzureSDKAutomation/azure-sdk-for-python | 9342 |
| 391886104 | wsbforg4/docker-repo-4 | 9293 |
| 9120498 | pypi/warehouse | 9232 |
| 487924901 | zpallenki/testing5 | 9158 |
| 116695811 | snyk-test/goof | 9122 |
| 156939672 | microsoft/onnxruntime | 9028 |
| 319742187 | mdn/translated-content | 9011 |
| 487811000 | zpallenki/testing1 | 8987 |
| 93446042 | hashicorp/terraform-provider-azurerm | 8956 |
| 16851223 | idaholab/moose | 8943 |
| 14303048 | mamedev/mame | 8932 |
| 179164257 | diem/diem | 8803 |
| 105783257 | yenkins/test-zuul | 8600 |
| 441297488 | yewalenikhil65/MethodOfLines.jl | 8599 |
| 206317 | apache/camel | 8566 |
| 13421878 | pentaho/pentaho-kettle | 8506 |
| 143040428 | github/codeql | 8449 |
| 29261473 | minio/minio | 8402 |
| 81815495 | KratosMultiphysics/Kratos | 8257 |
| 10601208 | umbraco/Umbraco-CMS | 8209 |
| 26194951 | KSP-CKAN/NetKAN | 8163 |
| 320636477 | WebKit/WebKit | 8074 |
| 28060246 | YetiForceCompany/YetiForceCRM | 7997 |
| 368199998 | TP-Lab/tokens | 7994 |
| 16286131 | mapbox/mapbox-gl-native | 7955 |
| 2158534 | wbond/package_control_channel | 7933 |
| 15019962 | tldr-pages/tldr | 7866 |
| 689344 | libretro/RetroArch | 7850 |
| 24107001 | open-mpi/ompi | 7813 |
| 6934395 | facebook/rocksdb | 7701 |
| 192960689 | AzureSDKAutomation/azure-sdk-for-net | 7671 |
| 201206239 | alt-how/altinn-studio | 7651 |
| 74627617 | commaai/openpilot | 7471 |
| 89275457 | learn-co-students/js-from-dom-to-node-bootcamp-prep-000 | 7460 |
| 34945282 | eliotsykes/real-world-rails | 7391 |
| 27507850 | batfish/batfish | 7347 |
| 7212645 | syl20bnr/spacemacs | 7322 |
| 32935745 | eclipse/che | 7314 |
| 174166640 | Cache-Cloud/tensorflow | 7128 |
| 256170081 | Samsung/ONE | 7046 |
| 2445970 | VoltDB/voltdb | 6996 |
| 1103607 | jenkinsci/jenkins | 6992 |
| 61980658 | azerothcore/azerothcore-wotlk | 6889 |
| 3314 | spree/spree | 6856 |
| 8162715 | saleor/saleor | 6783 |
| 116008723 | jenkins-x/jenkins-x-platform | 6774 |
| 133968906 | bbc/simorgh | 6747 |
| 538746 | ruby/ruby | 6630 |
| 149494920 | Scaleskope-Ind/Org_Apic_Auto_Repo | 6588 |
| 15171934 | Homebrew/homebrew-cask-fonts | 6527 |
| 88650014 | flutter/plugins | 6518 |
| 1514950 | cakephp/docs | 6514 |
| 11061773 | eslint/eslint | 6410 |
| 1318892 | silverstripe/silverstripe-framework | 6363 |
| 42552143 | dotnet/corert | 6361 |
| 7716883 | ankidroid/Anki-Android | 6357 |
| 166008577 | intel/llvm | 6310 |
| 25379390 | openucx/ucx | 6308 |
| 187305746 | mitchm69/formulae.brew.sh | 6271 |
| 289159034 | leanupjs/leanup | 6214 |
| 95035788 | MicrosoftDocs/office-docs-powershell | 6189 |
| 192019597 | dd-center/vdb | 6127 |
| 82206054 | weseek/growi | 6098 |
| 391367873 | Cardano-NFTs/policyIDs | 6002 |
| 43613404 | kubernetes-sigs/kubespray | 5951 |
| 2651887 | alphagov/smart-answers | 5930 |
| 37750722 | geneontology/go-ontology | 5922 |
| 178806582 | DimensionDev/Maskbook | 5913 |
| 6887813 | crystal-lang/crystal | 5905 |
| 12647552 | vlsergey/infosec | 5899 |
| 2228226 | ome/openmicroscopy | 5872 |
| 297949724 | boo-learn/SpecialistPython1 | 5825 |
| 17839626 | HGustavs/LenaSYS | 5803 |
| 121770401 | owncloud/web | 5748 |
| 433054141 | lokaliseAutomation/e2e-check-stage | 5745 |
| 222010497 | Rune-Status/runetech-osrs-gamepacks | 5727 |
| 175756913 | selfteaching/selfteaching-python-camp | 5705 |
| 52558133 | sonic-net/sonic-mgmt | 5693 |
| 33258973 | influxdata/telegraf | 5686 |
| 80945428 | nestjs/nest | 5632 |
| 48004987 | cockroachdb/docs | 5609 |
| 106462765 | microsoft/vscode-python | 5605 |
| 8582237 | splicemachine/spliceengine | 5530 |
| 7408108 | RT-Thread/rt-thread | 5507 |
| 66313046 | learn-co-students/javascript-arrays-bootcamp-prep-000 | 5485 |
| 354743391 | o3de/o3de | 5471 |
| 190277211 | Kab1r/tensorflow | 5463 |
| 123983554 | projectacrn/acrn-hypervisor | 5433 |
| 60016458 | Amsterdam/data-verkenner | 5430 |
| 7202769 | emqx/emqx | 5410 |
| 48798723 | beubiJenkins/for_testings | 5399 |
| 22074543 | dimagi/commcare-cloud | 5395 |
| 144689058 | JonathanSimon123/kubernetes | 5318 |
| 156018 | redis/redis | 5317 |
| 142967010 | AugurProject/augur | 5292 |
| 136633680 | openshift/installer | 5238 |
| 2028724 | cgeo/cgeo | 5236 |
| 312367855 | dynatrace-oss-contrib/opentelemetry-collector-contrib | 5216 |
| 302827809 | datafuselabs/databend | 5193 |
| 204742393 | shortlink-org/shortlink | 5186 |
| 57939112 | kubernetes/community | 5163 |
| 9678849 | empirical-org/Empirical-Core | 5150 |
| 67828134 | cri-o/cri-o | 5121 |
| 160750261 | microsoft/accessibility-insights-web | 5107 |
| 1926534 | gamerson/liferay-ide | 5084 |
| 191051391 | redwoodjs/redwood | 5078 |
| 103749180 | OpenGenus/cosmos | 5069 |
| 41847366 | flutter/website | 5066 |
| 53370988 | GoogleChrome/lighthouse | 5064 |
| 26826032 | pantheon-systems/documentation | 5052 |
| 44886691 | jenkins-infra/jenkins.io | 5048 |
| 354098767 | chaitanyaphalak/opentelemetry-collector-contrib | 5046 |
+-----------+---------------------------------------------------------+----------+
Hi, it's failed to execute question
Question title
The number of projects have got over 5000 PRs
Error message
Query execution timeout.
Generated SQL
Chart info