#2567 hub: use CTE for build_references
Merged a year ago by tkopecek. Opened 2 years ago by tkopecek.
tkopecek/koji issue2535c  into  master

file modified
+16 -12
@@ -8000,27 +8000,31 @@ 

      if build_rpm_ids:

          # psql planner gots confused if buildroot table is large (>trillion)

          # and len(rpm_ids) > ~500. In such case it switched to looped sequential scans

-         _dml("SET enable_hashjoin=off", {})

-         q = """SELECT MAX(create_event)

-                FROM standard_buildroot

-                WHERE buildroot_id IN (

-                  SELECT buildroot_id

+         # using "SET enabled_hashjoin=off" improved it for some cases. CTE could be slower for

+         # simple cases but would improve complicated ones.

+         q = """WITH buildroot_ids as (

+                  SELECT DISTINCT buildroot_id

                   FROM buildroot_listing

                   WHERE rpm_id IN %(rpm_ids)s

+                )

+                SELECT MAX(create_event)

+                FROM standard_buildroot

+                WHERE buildroot_id IN (

+                   SELECT buildroot_id FROM buildroot_ids

                 )"""

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

-         _dml("SET enable_hashjoin=on", {})

      if build_archive_ids:

-         _dml("SET enable_hashjoin=off", {})

-         q = """SELECT MAX(create_event)

-                FROM standard_buildroot

-                WHERE buildroot_id IN (

-                  SELECT buildroot_id

+         q = """WITH buildroot_ids as (

+                  SELECT DISTINCT buildroot_id

                   FROM buildroot_archives

                   WHERE archive_id IN %(archive_ids)s

+                )

+                SELECT MAX(create_event)

+                FROM standard_buildroot

+                WHERE buildroot_id IN (

+                   SELECT buildroot_id FROM buildroot_ids

                 )"""

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

-         _dml("SET enable_hashjoin=on", {})

          event_id = max(event_id, event_id2)

      if event_id:

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

rebased onto afca560

2 years ago

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

2 years ago

The testing ready tag was removed temporarily because of conflict with other PR.

Metadata Update from @mfilip:
- Pull-request untagged with: testing-ready

2 years ago

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

2 years ago

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

a year ago

The testing ready tag was removed temporarily because of conflict with other PR.

Metadata Update from @mfilip:
- Pull-request untagged with: testing-ready

a year ago

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

a year ago

Commit c39f6e9 fixes this pull-request

Pull-Request has been merged by tkopecek

a year ago