PR#1962: improve sql speed in build_references - koji

koji

#1962 improve sql speed in build_references

Merged 4 years ago by tkopecek. Opened 4 years ago by tkopecek.

tkopecek/koji issue1961 into master

improve sql speed in build_references

Tomas Kopecek • 4 years ago

dc56301

hub/kojihub.py

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))"""`

tkopecek commented 4 years ago

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

4 years ago