Uploaded image for project: 'Artifactory Binary Repository'
  1. Artifactory Binary Repository
  2. RTFACT-17668

A count(*) query misses the index on node table

    Details

    • Type: Performance
    • Status: Open
    • Priority: Normal
    • Resolution: Unresolved
    • Affects Version/s: 6.4.0
    • Fix Version/s: None
    • Component/s: Database, Performance
    • Labels:
      None
    • Environment:

      Artifactory 6.4.0 (dockerized) with >50M artifacts

      Description

      There is a query that is executed about 30 times daily on our installation of Artifactory. I have no idea where this query is initiated but it takes a long time to execute due to an index miss.

      2018-10-17 06:01:47.265 UTC SELECT loG:  duration: 133895.745 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 06:08:29.734 UTC SELECT loG:  duration: 183968.925 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 06:14:18.469 UTC SELECT loG:  duration: 134443.540 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 06:53:07.276 UTC SELECT loG:  duration: 130310.265 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 07:06:32.215 UTC SELECT loG:  duration: 121120.494 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 07:10:04.787 UTC SELECT loG:  duration: 124159.551 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 07:51:25.509 UTC SELECT loG:  duration: 129427.775 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 08:29:52.556 UTC SELECT loG:  duration: 130297.144 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 08:33:03.370 UTC SELECT loG:  duration: 129788.799 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 08:56:54.331 UTC SELECT loG:  duration: 130531.795 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 09:08:46.624 UTC SELECT loG:  duration: 129674.039 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 09:15:15.726 UTC SELECT loG:  duration: 133975.645 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 09:18:18.844 UTC SELECT loG:  duration: 125954.588 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 09:39:01.265 UTC SELECT loG:  duration: 132953.036 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 10:10:40.929 UTC SELECT loG:  duration: 173075.427 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 11:32:54.671 UTC SELECT loG:  duration: 154857.876 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 11:37:11.359 UTC SELECT loG:  duration: 136863.766 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 11:40:30.810 UTC SELECT loG:  duration: 133612.424 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 11:46:57.977 UTC SELECT loG:  duration: 139909.846 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 12:02:02.264 UTC SELECT loG:  duration: 155566.241 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 12:44:40.049 UTC SELECT loG:  duration: 139679.873 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 13:44:39.471 UTC SELECT loG:  duration: 135269.737 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 14:09:50.807 UTC SELECT loG:  duration: 174285.144 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 14:37:24.442 UTC SELECT loG:  duration: 129164.613 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 14:42:03.389 UTC SELECT loG:  duration: 129097.519 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 14:44:27.037 UTC SELECT loG:  duration: 130461.331 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 14:46:36.264 UTC SELECT loG:  duration: 127388.930 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 14:53:51.584 UTC SELECT loG:  duration: 138718.461 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 14:57:49.752 UTC SELECT loG:  duration: 134378.340 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 15:03:29.699 UTC SELECT loG:  duration: 130981.697 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 15:43:30.541 UTC SELECT loG:  duration: 127331.262 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 15:46:39.568 UTC SELECT loG:  duration: 114682.034 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      2018-10-17 15:49:20.658 UTC SELECT loG:  duration: 115285.624 ms  execute <unnamed>: SELECT COUNT(*) FROM nodes WHERE node_type = 1
      

      The query plan looks like this.

       Aggregate  (cost=4167794.48..4167794.49 rows=1 width=8)
         ->  Seq Scan on nodes  (cost=0.00..4039943.62 rows=51140343 width=0)
               Filter: (node_type = 1)
      

       

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              stefanga Stefan Gangefors
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: