#1962 improve sql speed in build_references
Merged 2 years ago by tkopecek. Opened 2 years ago by tkopecek.
tkopecek/koji issue1961  into  master

file modified
+16 -15
@@ -7721,22 +7721,23 @@ 

      # find timestamp of most recent use in a buildroot

      event_id = 0

      if build_rpm_ids:

-         query = QueryProcessor(

-                     columns=['max(standard_buildroot.create_event)'],

-                     tables=['buildroot_listing'],

-                     joins=['standard_buildroot ON buildroot_listing.buildroot_id = standard_buildroot.buildroot_id'],

-                     clauses=['buildroot_listing.rpm_id IN %(rpm_ids)s'],

-                     values={'rpm_ids': build_rpm_ids})

-         event_id = query.singleValue(strict=False) or 0

- 

+         q = """SELECT MAX(create_event)

+                FROM standard_buildroot

+                WHERE buildroot_id IN (

+                  SELECT buildroot_id

+                  FROM buildroot_listing

+                  WHERE rpm_id IN %(rpm_ids)s

+                )"""

+         event_id = _fetchSingle(q, {'rpm_ids': build_rpm_ids}) or 0

      if build_archive_ids:

-         query = QueryProcessor(

-                     columns=['max(standard_buildroot.create_event)'],

-                     tables=['buildroot_archives'],

-                     joins=['standard_buildroot ON buildroot_archives.buildroot_id = standard_buildroot.buildroot_id'],

-                     clauses=['buildroot_archives.archive_id IN %(archive_ids)s'],

-                     values={'archive_ids': build_archive_ids})

-         event_id2 = query.singleValue(strict=False) or 0

+         q = """SELECT MAX(create_event)

+                FROM standard_buildroot

+                WHERE buildroot_id IN (

+                  SELECT buildroot_id

+                  FROM buildroot_archives

+                  WHERE archive_id IN %(archive_ids)s

+                )"""

+         event_id2 = _fetchSingle(q, {'archive_ids': build_archive_ids}) or 0

          event_id = max(event_id, event_id2)

      if event_id:

          q = """SELECT EXTRACT(EPOCH FROM get_event_time(%(event_id)i))"""

Fixes: https://pagure.io/koji/issue/1961

Original:

explain analyze SELECT max(standard_buildroot.create_event) FROM buildroot_listing JOIN standard_buildroot ON buildroot_listing.buildroot_id = standard_buildroot.buildroot_id WHERE buildroot_listing.rpm_id IN (849093, 849094);
                                                                          QUERY PLAN                                                                          
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=282987.68..282987.69 rows=1 width=4) (actual time=2269.508..2269.508 rows=1 loops=1)
   ->  Hash Join  (cost=174746.48..282779.51 rows=83267 width=4) (actual time=2009.133..2269.493 rows=7 loops=1)
         Hash Cond: (buildroot_listing.buildroot_id = standard_buildroot.buildroot_id)
         ->  Index Scan using buildroot_listing_rpms on buildroot_listing  (cost=0.58..85733.35 rows=83267 width=4) (actual time=0.020..0.050 rows=7 loops=1)
               Index Cond: (rpm_id = ANY ('{849093,849094}'::integer[]))
         ->  Hash  (cost=90382.29..90382.29 rows=5142129 width=8) (actual time=1921.891..1921.891 rows=5146457 loops=1)
               Buckets: 131072  Batches: 8  Memory Usage: 25146kB
               ->  Seq Scan on standard_buildroot  (cost=0.00..90382.29 rows=5142129 width=8) (actual time=0.005..820.152 rows=5146457 loops=1)
 Planning time: 0.309 ms
 Execution time: 2274.669 ms

and postgres log:

020-01-21 13:37:01 UTC [14464]: [147-1] user=brew,db=brew LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp14464.102", size 17998736
2020-01-21 13:37:01 UTC [14464]: [148-1] user=brew,db=brew STATEMENT:  explain analyze SELECT max(standard_buildroot.create_event) FROM buildroot_listing JOIN standard_buildroot ON buildroot_listing.buildroot_id = standard_buildroot.buildroot_id WHERE buildroot_listing.rpm_id IN (849093, 849094);

subselect:

 explain analyze SELECT max(create_event) FROM standard_buildroot where buildroot_id in (select buildroot_id from buildroot_listing WHERE rpm_id IN (849093, 849094));                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=86017.53..86017.54 rows=1 width=4) (actual time=0.251..0.251 rows=1 loops=1)
   ->  Nested Loop  (cost=85941.95..85954.91 rows=25051 width=4) (actual time=0.163..0.246 rows=7 loops=1)
         ->  HashAggregate  (cost=85941.52..85941.54 rows=2 width=4) (actual time=0.122..0.125 rows=7 loops=1)
               Group Key: buildroot_listing.buildroot_id
               ->  Index Scan using buildroot_listing_rpms on buildroot_listing  (cost=0.58..85733.35 rows=83267 width=4) (actual time=0.070..0.112 rows=7 loops=1)
                     Index Cond: (rpm_id = ANY ('{849093,849094}'::integer[]))
         ->  Index Scan using standard_buildroot_pkey on standard_buildroot  (cost=0.43..6.67 rows=1 width=8) (actual time=0.015..0.016 rows=1 loops=7)
               Index Cond: (buildroot_id = buildroot_listing.buildroot_id)
 Planning time: 0.461 ms
 Execution time: 0.313 ms

Metadata Update from @tkopecek:
- Pull-request tagged with: testing-ready

2 years ago

Metadata Update from @jcupova:
- Pull-request tagged with: testing-done

2 years ago

Commit 9d25189 fixes this pull-request

Pull-Request has been merged by tkopecek

2 years ago
Metadata