launchpad_dogfood=# EXPLAIN (ANALYZE, BUFFERS) SELECT name FROM (SELECT name FROM sourcepackagename WHERE name LIKE '%linux%' AND id IN (SELECT sourcepackagename FROM distributionsourcepackagecache WHERE archive IN (SELECT id FROM archive WHERE purpose IN (1, 4)) OR archive IS NULL) ORDER BY name LIMIT 10) AS spn UNION (SELECT name FROM binarypackagename WHERE name LIKE '%linux%' AND id IN (SELECT binarypackagename FROM distroseriespackagecache WHERE archive IN (SELECT id FROM archive WHERE purpose IN (1, 4))) ORDER BY name LIMIT 10) ORDER BY name LIMIT 10;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=83127.97..83127.99 rows=10 width=22) (actual time=1440.034..1440.036 rows=10 loops=1)
Buffers: shared hit=16759 read=157392, temp read=2811 written=2797
-> Sort (cost=83127.97..83128.02 rows=20 width=22) (actual time=1440.033..1440.034 rows=10 loops=1)
Sort Key: sourcepackagename.name
Sort Method: quicksort Memory: 26kB
Buffers: shared hit=16759 read=157392, temp read=2811 written=2797
-> HashAggregate (cost=83127.34..83127.54 rows=20 width=22) (actual time=1440.016..1440.019 rows=19 loops=1)
Buffers: shared hit=16759 read=157392, temp read=2811 written=2797
-> Append (cost=13352.34..83127.29 rows=20 width=22) (actual time=163.072..1439.997 rows=20 loops=1)
Buffers: shared hit=16759 read=157392, temp read=2811 written=2797
-> Limit (cost=13352.34..13352.37 rows=10 width=15) (actual time=163.072..163.076 rows=10 loops=1)
Buffers: shared hit=10471 read=2277
-> Sort (cost=13352.34..13353.20 rows=344 width=15) (actual time=163.070..163.074 rows=10 loops=1)
Sort Key: sourcepackagename.name
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=10471 read=2277
-> Hash Join (cost=13109.31..13344.91 rows=344 width=15) (actual time=148.811..162.949 rows=213 loops=1)
Hash Cond: (distributionsourcepackagecache.sourcepackagename = sourcepackagename.id)
Buffers: shared hit=10471 read=2277
-> HashAggregate (cost=12502.65..12672.74 rows=17009 width=4) (actual time=144.743..152.920 rows=33722 loops=1)
Buffers: shared hit=10463 read=1923
-> Index Only Scan using distributionsourcepackagecache__distribution__sourcepackagename on distributionsourcepackagecache (cost=139.86..12193.67 rows=123594 width=4) (actual time=0.108..117.097 rows=68143 loops=1)
Filter: ((hashed SubPlan 1) OR (archive IS NULL))
Rows Removed by Filter: 179046
Heap Fetches: 2
Buffers: shared hit=10463 read=1923
SubPlan 1
-> Index Scan using archive__distribution__purpose__distro_archives__key on archive archive_1 (cost=0.14..139.27 rows=69 width=4) (actual time=0.006..0.057 rows=38 loops=1)
Index Cond: (purpose = ANY ('{1,4}'::integer[]))
Buffers: shared hit=38
-> Hash (cost=596.20..596.20 rows=837 width=19) (actual time=3.749..3.749 rows=1059 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 63kB
Buffers: shared hit=8 read=354
-> Bitmap Heap Scan on sourcepackagename (cost=50.48..596.20 rows=837 width=19) (actual time=0.919..3.263 rows=1059 loops=1)
Recheck Cond: (name ~~ '%linux%'::text)
Buffers: shared hit=8 read=354
-> Bitmap Index Scan on sourcepackagename__name__trgm (cost=0.00..50.28 rows=837 width=0) (actual time=0.847..0.847 rows=1059 loops=1)
Index Cond: (name ~~ '%linux%'::text)
Buffers: shared hit=6 read=10
-> Limit (cost=69774.59..69774.62 rows=10 width=30) (actual time=1276.916..1276.918 rows=10 loops=1)
Buffers: shared hit=6288 read=155115, temp read=2811 written=2797
-> Sort (cost=69774.59..69780.57 rows=2392 width=30) (actual time=1276.915..1276.916 rows=10 loops=1)
Sort Key: binarypackagename.name
Sort Method: top-N heapsort Memory: 25kB
Buffers: shared hit=6288 read=155115, temp read=2811 written=2797
-> Hash Semi Join (cost=63945.16..69722.90 rows=2392 width=30) (actual time=1052.818..1273.190 rows=14610 loops=1)
Hash Cond: (binarypackagename.id = distroseriespackagecache.binarypackagename)
Buffers: shared hit=6288 read=155115, temp read=2811 written=2797
-> Bitmap Heap Scan on binarypackagename (cost=790.26..5553.53 rows=68421 width=34) (actual time=27.037..58.386 rows=63475 loops=1)
Recheck Cond: (name ~~ '%linux%'::text)
Buffers: shared hit=8 read=3743
-> Bitmap Index Scan on binarypackagename__name__trgm (cost=0.00..773.16 rows=68421 width=0) (actual time=26.097..26.097 rows=63475 loops=1)
Index Cond: (name ~~ '%linux%'::text)
Buffers: shared hit=6 read=156
-> Hash (cost=62943.49..62943.49 rows=16913 width=4) (actual time=1025.597..1025.597 rows=629201 loops=1)
Buckets: 2048 Batches: 8 (originally 1) Memory Usage: 5121kB
Buffers: shared hit=6280 read=151372, temp written=1609
-> Nested Loop (cost=0.57..62943.49 rows=16913 width=4) (actual time=0.033..800.889 rows=629201 loops=1)
Buffers: shared hit=6280 read=151372
-> Index Scan using archive__distribution__purpose__distro_archives__key on archive (cost=0.14..139.27 rows=69 width=4) (actual time=0.009..0.048 rows=38 loops=1)
Index Cond: (purpose = ANY ('{1,4}'::integer[]))
Buffers: shared hit=38
-> Index Scan using distroseriespackagecache__archive__idx on distroseriespackagecache (cost=0.43..907.76 rows=245 width=8) (actual time=0.005..17.564 rows=16558 loops=38)
Index Cond: (archive = archive.id)
Buffers: shared hit=6242 read=151372
Total runtime: 1440.218 ms
(66 rows)